ropenxlsx

How to do conditional formatting a cell based on another column

I have following df and could like to output to an Excel file. Is it possible to add a highlight to S1:S5 with a condition Cell Value = Value in Line.

I did look through the condtionalFormatting() but fail to figure out how to move further. Could anyone guide me on this? Thanks.

enter image description here

df <- structure(list(ID = c(1001, 1001, 1001, 1001, 1001, 1001, 1001, 
1001, 1001), Line = c(98, 79, 25, 88, 66, 87, 25, 36, 99), S1 = c(99, 
66, 25, NA, NA, NA, NA, 36, 99), S2 = c(98, 27, NA, 88, 77, NA, 
58, NA, NA), S3 = c(98, 99, 49, NA, 66, NA, NA, 36, 89), S4 = c(88, 
79, NA, NA, NA, NA, 25, NA, 55), S5 = c(99, NA, NA, NA, 89, 87, 
NA, NA, 96)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-9L))

library(openxlsx)
wb <-  createWorkbook()
writeData(wb, sheet = "Q1", x = df, colNames = T)
saveWorkbook(wb, "C:/Users/Documents/df.xlsx"), overwrite = TRUE)
Solution

  • One way could be to use condtionalFormatting() with rule = "C2=$B2" to compare the cell values with column "line". Adjust the style (background/fontcolor) in createStyle to your liking.

    dd <- structure(
  list(
    ID = c(1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001),
    Line = c(98, 79, 25, 88, 66, 87, 25, 36, 99),
    S1 = c(99, 66, 25, NA, NA, NA, NA, 36, 99),
    S2 = c(98, 27, NA, 88, 77, NA, 58, NA, NA),
    S3 = c(98, 99, 49, NA, 66, NA, NA, 36, 89),
    S4 = c(88, 79, NA, NA, NA, NA, 25, NA, 55),
    S5 = c(99, NA, NA, NA, 89, 87, NA, NA, 96)
  ),
  class = c("tbl_df", "tbl", "data.frame"),
  row.names = c(NA, -9L)
)

library(openxlsx)

wb <- createWorkbook()
addWorksheet(wb, "Q1")
writeData(wb, sheet = "Q1", x = dd, colNames = TRUE)

conditionalFormatting(
  wb = wb,
  sheet = "Q1",
  cols = 3:7,  
  rows = 2:(nrow(dd) + 1),  
  rule = "C2=$B2",  
  style = createStyle(bgFill = "lightblue3")
)

saveWorkbook(wb, "C:/Users/Documents/df.xlsx", overwrite = TRUE)

    out