Conditional formatting DT datatable using styleInterval and rule

Question

Hi I have a rendered datatable in which I want to conditionally format the Roll_3_Weeks column based off the value in the Target column.

I have tried with styleInterval but I want the formatting to be based off a percentage and the function seems to want whole numbers.

Specifically, I want to color the Roll_3_Weeks cell green if it is within 95% of the Target value*3 and red in all other instances.

An image of my table is below and here is my current code snippet Thanks in advance!

 output$backs_week_table <- renderDT({
    datatable(Backs_td_week,
              extensions = 'FixedColumns',
              options = list(
                pageLength = 15,
                fixedColumns = list(leftColumns = 3),
                dom = 't', # Show only the table without search and pagination
                columnDefs = list(
                  list(targets = c(0, 1), className = 'dt-left'), # Left-align the first two columns
                  list(targets = seq(1, ncol(Backs_td_week) - 1), className = 'dt-center') # Center-align columns starting from the third column 
                ),
                rowCallback = JS('function(row, data, index) {
                                 $("td", row).css("padding-top", "5px");
                                 $("td", row).css("padding-bottom", "5px");
                               }')
              ),
              rownames = FALSE
    ) %>%
      formatStyle(
        names(Backs_td_week),
        border = '1px solid #ddd' # Add cell borders
      ) %>%
      formatStyle(
        names(Backs_td_week)[grepl("Perc_", names(Backs_td_week))],
        valueColumns = names(Backs_td_week)[grepl("Perc_", names(Backs_td_week))],
        color = 'black',
        background = styleColorBar(
          sapply(Backs_td_week[, grepl("Perc_", names(Backs_td_week))], function(x) scales::rescale(x, to = c(0, 100))), 'indianred')
      )
  })

Answer 1

Using DT, you could add a helper valueColumn which, though hidden in the output, decides the style of another column.

Example:

## sample data:
d <- data.frame(Roll_3_Weeks = c(1, 3), Target = 1)
d$is_extreme <- ifelse(d$Roll_3_Weeks <= .95 * 3 * d$Target, 0, 1)

## > d
##   Roll_3_Weeks Target is_extreme
## 1            1      1          0
## 2            3      1          1

datatable(d, 
          options = list(
            ## hide helper column 'is_extreme':
            columnDefs = list(list(targets = 'is_extreme', visible = FALSE))
          )
          ) |>
  formatStyle(
    columns = 'Roll_3_Weeks', valueColumns = 'is_extreme',
    backgroundColor = styleEqual(c(0, 1), c('green', 'red'))
    )
  )