Color cell in excel based on number of characters.

excel
xlsx
writexl
#1

How to use openxlsx::conditionalFormatting to check number of charachters in cell and then color if length of string less than 10.

not able to write the "rule"

0 Likes

#2

I do not have Excel with which to test this but it works when I open the xlsx file in OpenOffice. Cells B1 and B3 have a light red background. The code is largely from the openxlsx package documentation.

library(openxlsx)

wb <- createWorkbook()
addWorksheet(wb, "MySheet")
negStyle <- createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE")
writeData(wb, "MySheet", c("ABCDE", "ABCDEFGHIJK", "AB"), startCol=2)
conditionalFormatting(wb, "MySheet", cols=2, rows=1:3, rule="LEN(B1)<10", style = negStyle)
saveWorkbook(wb, "ExcelOut.xlsx", overwrite = TRUE)
0 Likes