Forum Discussion

mbinkley8's avatar
mbinkley8
Copper Contributor
Apr 06, 2024

Conditional Formatting formula help

All,

 

I am trying to write a conditional formatting formula that will highlight an entire row of a table based on the value of each cell in the row being above or below a baseline.  As an example, if the 4 values in the row are -10, 10, -15, and 20 and the baseline values are 5, and -5 then the row in the table should be highlighted, but if the 4 values in the row are 2, -10, -15, and 20, then the row should not be highlighted.  If anyone has any ideas, I would really appreciate the help!

 

Thanks!

  • mbinkley8 

    Select the data body of the table (i.e. the table without its header row).

    In the following, I'll assume that the active cell in the selection is in row 2 of the sheet, and that the four columns are B, C, D and E.

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Use a formula to determine which cells to format'.
    Enter the formula

    =COUNTIFS($B2:$E2, "<=5", $B2:$E2, ">=-5")=0

    Click Format...
    Activate the Fill tab.
    Select a highlight color.
    Click OK, then click OK again.

     

    You could place the baseline values in cells instead of specifying them in the formula itself.

    For example, if the high baseline is in K1 and the low one in K2, use the formula

     

    =COUNTIFS($B2:$E2, "<="&$K$1, $B2:$E2, ">="&$K$2)=0

Resources