How to use openxlsx to conditionally format only columns that meet a specific criteria?

I am trying to use openxlsx to conditionally format an Excel with R. In the examples online I see columns specified as numbers, hardcoded like below. I need to conditionally format certain columns that contain a keyword though and not rely on the column numbers themselves.

The following works as expected, but I need to change the i in 11:13 to reference columns with keywords.

for (i in 11:13){
  conditionalFormatting(wb, "data",
                        cols = i,
                        rows = 2:nrow(data), rule = "<$D2", style = negStyle
  )
}


I have tried using checkk <- data[ , grep("Dog", colnames(data))] to select columns that have the relevant keyord, Dog, contained in the column name, and to sequence along this newly created data frame. This does not conditionally format correctly though.

#get the columns to conditionally format
checkk <- data[ , grep("Dog", colnames(data))]

for (i in seq_along(checkk)){
  conditionalFormatting(wb, "data",
                        cols = i,
                        rows = 2:nrow(data), rule = "<$D2", style = negStyle
  )
}

I have also tried to specify colnames to loop through the relevant column names that include Dog and conditionally format those columns. This also does not format correctly.

#get the columns to conditionally format
checkk <- data[ , grep("Dog", colnames(data))]

for (i in colnames(checkk)){
  conditionalFormatting(wb, "data",
                        cols = i,
                        rows = 2:nrow(data), rule = "<$D2", style = negStyle
  )
}

Does anyone know how to use conditionalFormatting() only on certain columns that contain a partial, case sensitive string (Dog in the column name in this example)?

I have not tested this but I think it will work.

COLS <- grep("Dog", colnames(data))
conditionalFormatting(wb, "data",
                      cols = COLS,
                      rows = 2:nrow(data), rule = "<$D2", style = negStyle
)

Thank you! This did the trick. Looks like I needed to grab the index and not the name. Appreciate the assistance - marked your answer as the solution.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.