Set FlexTable color in a row based on the value in another row of the same column and do this across all columns

Question

I have some data in a flextable and I need to color the text in row 5 ("Some text") of all columns and have the color depending on the values of rows 2, 3, and 4.

For example, if the value in row 2 is between 4 and 10, the value in row 3 is > 2, and the value in row 4 is < 0.25, I want to color the text in row 5 red. If the value in row 2 is less than 4, regardless of the values in rows 3 and 4, I would like to color the text in row 5 gray.

Using the data below, row 5 of column 1 would be gray since row 2 is less than 4 and row 5 of column 3 would be red, since the row 2 value is between 4 and 10, the value in row 3 is greater than 2 and the value in row 4 is less than 0.25.

# Some data 
SalesData <- data.frame(V1 = c("A", 1, 0, 1.0, "Some text"), 
                        V2 = c("B", 14, 0, 0.8, "Some text"), 
                        V3 = c("C", 4, 3, 0.20, "Some text"))

Any help would be greatly appreciated.

Answer 1

You can format the color of given cells this way. rules are not exactly the same as yours but you can adapt easily.

library(flextable)
library(officer)
library(dplyr)

SalesData <- data.frame(V1 = c("A", 1, 0, 1.0, "Some text"), 
                        V2 = c("B", 14, 0, 0.8, "Some text"), 
                        V3 = c("C", 4, 3, 0.20, "Some text"))
ft <- flextable(SalesData)

colnb <- ncol(SalesData)
for (col in 1:colnb) {
  if (SalesData[2, col] >= 1 & SalesData[2, col] < 10 & SalesData[4, col] >= 0.5) {
    ft <- style(ft, i = 5, j = col, 
        pr_t = fp_text_default(
          shading.color = "red"))
  } else {
    ft <- style(ft, i = 5, j = col, 
        pr_t = fp_text_default(
          shading.color = "blue"))
  }
}

autofit(ft)