How do I conditionally format an Excel according to another column? I am trying to color code numbers in columns 1 and columns 2 according to if they are less than the values in the column value.

This code snippet right below works, but I want to replace the rule = "<500" with a column so it is more dynamic. I keep getting errors and have tried a few things.

library(openxlsx)
library(tidyverse)

wb <- createWorkbook() #create workbook
addWorksheet(wb, "sheet_dog") #create blank sheet called sheet_dog
writeData(wb, "sheet_dog",dataset) # populate blank sheet_dog sheet with data

negStyle <- createStyle(fontColour = "#9C0006") #select color we will color code with 

conditionalFormatting(wb, "sheet_dog", #specify sheet and workbook
                      cols = 2, #color code column 2
                      rows = 1:nrow(dataset), rule = "<500", style = negStyle #rule
)

I have tried replacing this rule = "<500" with rule = "<dataset[c('value')]" (I thought this would color values in col 2 red if they were less than the value in value column). I have also tried simply writing the column, rule = "<value" and have tried rule = "<dataset$value".

How would I conditionally format a column according to the value in another column? What am I doing wrong? Thank you.

As a visual for clarity, I have created a table and bolded what I would hope to highlight red in an Excel here:

CodePudding user response：

I haven't worked with R, but the formula in Excel would be

=A2<$C2

I would then apply that rule to cells A2:B3 with the desired formatting.

CodePudding user response：

conditionalFormatting(wb, "sheet_dog", #specify sheet and workbook
                      cols = 2, #color code column 2
                      rows = 2:nrow(dataset), rule = "<$C2", style = negStyle #rule
)

If anyone is curious, in Excel, putting the $ there locks onto that column but the row can be iterated through. So, the code above, goes from the second row (to avoid including headers) through the entirety of the dataset and compares to the corresponding column, starting at C2.