Forum Discussion
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,...
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
