Forum Discussion
Solved
Conditional Formatting- Greater than multiple cells
Hello, Im trying to conditionally format cells that are greater than either values from 2 other cells: I want to colour any of the white shaded cells in the same row that are greater than ...
- a custom formula in conditional formatting will apply the formula to the top left most cell in the 'Applied to' range and then move to each other cell in the range and adjust the formula relative to that next cell location. So lets pretend the gray columns are A:B and you highlight columns C:I and create new rule. Then Enter:
=ISNUMBER(C1)*((C1>$A1)+(C1>$B1))
So C1 is the top left corner of the range so If C1 is a number AND > either A1 or B1 it is true
Then when excel looks at D1 it replaces each C1 with D1 in the formula but the $ before the A and the B tell it to Lock them and not change. And similar when it goes to row 2 all the 1s become 2s.
Let's say that the range you want to format conditionally is D2:J50, with the cells in gray in C2:C50
Select D2:J50. The active cell in the selection should be in row 2.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop-down set to 'Cell Value'.
Select 'greater than' from the second drop-down.
Enter the formula =MIN($B2:$C2) in the box next to it. Remember, B and C are the columns shaded gray, and 2 is the row number of the active cell.
Click Format...
Activate the Fill tab.
Select a color.
Click OK, then click OK again.