openxlsx how to highlight cells by comparing with another sheet

Question

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 

Answer 1

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

• If you want to compare cell to cell (so A1 vs A1, not A1 vs all of sheet 1),and highlight instances they're equal the rule should be "=A1 = Sheet2!A1".
• If you want to compare cell to cell but highlight instances where they aren't equal the rule should be "=A1 <> Sheet2!A1"
• If you want to apply the style to the cell if it's anywhere in sheet 1, try this tutorial, whatever you would enter as the formula for the rule if you were working in excel is what gets put into the rule argument of the conditionalFormattingfunction