Forum Discussion
Conditional formatting using date passed and value in another column
Select the cells in column C, starting in C2. C2 should be the active cell in the selection.
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.
In the box next to it, enter the formula
=IF(I2<7, 60, 90)
Click Format...
Activate the Fill tab.
Select red.
Click OK, then click OK again.
Have you actually tried my suggestion? It's simpler than Riny_van_Eekelen's (excellent!) solution.
- Wow - thanks Hans and apologies for my earlier reply: I don't understand how it does it, but it works! A very elegant formula... how is that interpreting levels in column i that are less than 7 and applying the 60 day rule? Brilliant anyway, thank you both for these solutions
Since we select 'greater than' from the second drop down, the rule will highlight cells whose value is greater than the result of the formula in the box next to the second drop down.
The formula =IF(I2<7, 60, 90) returns 60 if I2<7, i.e. if I2 is 1, 2, 3, 4, 5, or 6. So in those cases, the cell will be highlighted if its value is greater than 60.
But the formula returns 90 if I2 is not <7, i.e. if I2 is 7, 8, or 9. In those cases, the cell will be highlighted if its value is greater than 90.