Forum Discussion
Conditional formatting using date passed and value in another column
Hi please could anyone help with this - I'm trying to use an 'AND' formula but seem to be messing it up I have a sheet tracking projects, showing the date open - the date is in column B, and colu...
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.
Thanks Hans, appreciated. Think this would highlight anything with specific days rather than looking at eg level 6 projects that are over 60 days or level 7 projects over 90 days. Since solved. Thanks again, it's really useful learning as can imagine the formula you've shown will also come in handy in future 🙂
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.