How to color code values with `openxlsx::conditionalFormatting`?

Question

How do I use openxlsx::conditionalFormatting on a list of column indexes, not necessarily in order? In the documentation, ?conditionalFormatting, all the examples fill the cols argument with a : like cols = 1:5 Meaning 1,2,3,4,5; however, I want my columns to be color coded according to if their index is in a list. The column index isn’t necessarily in a numerical order like 1:5. It could be 1,2,4,6,8 or something like that.

As an example:

library(tidyverse)
# install.packages("openxlsx")
library(openxlsx)
library(writexl)
library(glue)

data_format <- data.frame(vals = c(5,6,2,12,5,12,5,4.5,12,13,3,15,17,30,7,19),
                      vals1 = c(2,6,2,12,13,12,5,4.5,12,13,3,15,19,30,7,9),
                      vals2 = c(2,7,2,7,13,12,5,4.5,12,1,3,15,20,30,7,6),
                      vals3 = c(1,20,2,8,12,1,1,9,4.2,16,11,3,14,10,28,5),
                      vals4 = c(5,13,2,12,13,12,1,4.5,12,10,3,15,20,29,7,9),
                      vals5 = c(5,15,2,10,18,11,3,4.5,12,13,2,15,86,90,9,11),
                      thresh1 = c(4,11,9,13.5,12,12,6,4.8,10,14,3,17,22,80,8,13),
                      thresh2 = c(6,12,1,13,16,11,5,3,16,12,1,13,19,20,6,10))

data_format <-
  data_format %>%
  relocate(thresh1, .before=vals)

data_format <-
  data_format %>%
  relocate(thresh2, .after=thresh1)

wb <- createWorkbook()


addWorksheet(wb, "data_format")

writeData(wb, "data_format",data_format)

Colpink1 <- c(4,5,6,8) # I would expect these columns to be pink when they are less than column A

Colpurple2 <- c(3,7) # I would expect these columns to be purple when they are less than column B

pinkStyle <- createStyle(fontColour = "#FA977C")
purpleStyle <- createStyle(fontColour = "#9B7CFA")

conditionalFormatting(wb, "data_format",
                      cols = Colpink1,
                      rows = 2:nrow(data_format), rule = "<$A2", style = pinkStyle
)

conditionalFormatting(wb, "data_format",
                      cols = Colpurple2,
                      rows = 2:nrow(data_format), rule = "<$B2", style = purpleStyle
)





filepath <-
  glue("PATH/format_coloring.xlsx") 

saveWorkbook(wb, file = filepath)

Column C is purple as expected but G has pink and purple values. I would want G to just be color coded according to purple. The other columns have a mixture of pink and purple where I would expect pink. Does anyone have an idea on how to conditionally format according to index not in order?

If anyone has ideas that would be appreciated.

user_11650909 · Answer

The cols argument looks like it takes ranges, even though you specify c(4,5,6,8) that is interpreted as columns 4-8 (including column 7). You could loop through Colpink1 and Colpurple2, applying the conditional formatting

wb <- createWorkbook()



addWorksheet(wb, "data_format")

writeData(wb, "data_format",data_format)

Colpink1 <- c(4,5,6,8) 

Colpurple2 <- c("C","G") #just testing that column letters worked too

pinkStyle <- createStyle(fontColour = "#FA977C")
purpleStyle <- createStyle(fontColour = "#9B7CFA")

for(i in Colpink1){
conditionalFormatting(wb, "data_format",
                      cols = i,
                      rows = 2:nrow(data_format), rule = "<$A2", style = pinkStyle)
}

for(i in Colpurple2){
conditionalFormatting(wb, "data_format",
                      cols = i,
                      rows = 2:nrow(data_format), rule = "<$B2", style = purpleStyle)
}

openXL(wb)

How to color code values with `openxlsx::conditionalFormatting`?

1 Answers