openxlsx

openxlsx how to highlight cells by comparing with another sheet


I want to compare two sheets values, and highlight the difference, below is a simple example, can someone help me to out about how to write the rule=? Thanks

wb \<- createWorkbook()
addWorksheet(wb, "sheet1")
addWorksheet(wb, "sheet2")

equalStyle \<- createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE")

writeData(wb, "sheet1", -5:5)
writeData(wb, "sheet1", -3:7, startCol = 2)

writeData(wb, "sheet2", 7:-3)
writeData(wb, "sheet2", 5:-5, startCol = 2)

conditionalFormatting(wb, "sheet2",
cols = 1:2,
rows = 1:11, rule = "**sheet1!A1 == sheet2!A1**", style = equalStyle 

Solution

  • I'm not entirely sure what you want but hopefully one of these gets what you need: