Conditional row formatting

[AnnePdM]

I would like to be able to format the text (say red) in a whole excel row based on a value in one (or more) of the columns. Can anyone help? To do this, I was playing around with the mtcars dataset. For example, I would like to highlight the rows where mpg is less than 20 and vs is equal to one. I think that is three rows.
The only examples I can find seem to relate to highlighting single cells rather than whole rows.

[JanMarvin]

Hi @AnnePdM , something like this?

library(openxlsx2)

# create style
negStyle <- create_dxfs_style(font_color = wb_colour(hex = "FF9C0006"),
                              bgFill = wb_colour(hex = "FFFFC7CE"))

# create custom row
dat <- mtcars
dat$hl <- dat$mpg < 20 & dat$vs == 1

# get excel column name
rulerow <- int2col(ncol(dat))

# create workbook add data
wb <- wb_workbook()$
  add_worksheet()$
  add_data(x = dat)

# add style to workbook
wb$styles_mgr$add(negStyle, "negStyle")

# add conditional formatting
wb$add_conditional_formatting(
  "Sheet 1",
  cols = seq_along(dat),
  rows = seq_len(nrow(dat)) + 1,
  rule = sprintf("$%s2=TRUE", rulerow),
  style = "negStyle"
)

[AnnePdM]

Thanks that's perfect, I would never have got there on my own!

[JanMarvin]

Glad it helps. It's a learning experience for all of us :)

[JanMarvin]

Another solution is this - stumbled over here (tested with LibreOffice). Using AND() to combine two Excel formulas. Wasn't even aware that this possibility exists.

library(openxlsx2)

# create style
negStyle <- create_dxfs_style(font_color = wb_colour(hex = "FF9C0006"),
                              bgFill = wb_colour(hex = "FFFFC7CE"))

# create workbook add data
wb <- wb_workbook()$
  add_worksheet()$
  add_data(x = mtcars)

# add style to workbook
wb$styles_mgr$add(negStyle, "negStyle")

# add conditional formatting
wb$add_conditional_formatting(
  "Sheet 1",
  cols = seq_along(mtcars),
  rows = seq_len(nrow(mtcars)) + 1,
  rule = "AND($A2<20,$H2=1)",
  style = "negStyle"
)

[AnnePdM]

Thanks! From: Jan Marvin Garbuszus ***@***.***> Sent: 09 December 2022 17:19 To: JanMarvin/openxlsx2 ***@***.***> Cc: AnnePdM ***@***.***>; Mention ***@***.***> Subject: Re: [JanMarvin/openxlsx2] Conditional row formatting (Discussion #433) Another solution is this - stumbled over here <https://stackoverflow.com/questions/74726485/openxlsx-conditional-formatting-with-multiple-rules/74726809#comment131886802_74726485> (tested with LibreOffice). Using AND() to combine two Excel formulas. Wasn't even aware that this possibility exists. library(openxlsx2) # create style negStyle <- create_dxfs_style(font_color = wb_colour(hex = "FF9C0006"), bgFill = wb_colour(hex = "FFFFC7CE")) # create workbook add data wb <- wb_workbook()$ add_worksheet()$ add_data(x = mtcars) # add style to workbook wb$styles_mgr$add(negStyle, "negStyle") # add conditional formatting wb$add_conditional_formatting( "Sheet 1", cols = seq_along(mtcars), rows = seq_len(nrow(mtcars)) + 1, rule = "AND($A2<20,$H2=1)", style = "negStyle" ) — Reply to this email directly, view it on GitHub <#433 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/AC4XIQMU3PMH2QWHH6JIGDDWMNS2DANCNFSM6AAAAAASJFI2L4> . You are receiving this because you were mentioned. <https://github.com/notifications/beacon/AC4XIQIOHQOXRICVC26IFDTWMNS2DA5CNFSM6AAAAAASJFI2L6WGG33NNVSW45C7OR4XAZNRIRUXGY3VONZWS33OINXW23LFNZ2KUY3PNVWWK3TUL5UWJTQAIJYM6.gif> Message ID: ***@***.*** ***@***.***> >