 |
| |||
|
Excel Change Cell Color on Condition How to change the color of a cell based on the value of another cell This example changes the color of the Name cell based on the StartDate If the start date was 90 days ago or more the Name Cell color changes The key to this color change is the formula:- =TODAY()-INDEX($A$1:$D$7,ROW(),2)>89 This formula takes the value in column 2 (StartDate) for the current Row and subtracts it from Todays Date leaving a number of days. This value is compared with 89 and if it is greater the Name Cell color is altered by the Main Formatting rule as detailed below. The Column Diff is not required it is just there to show the actual No. of Days between the date and today. The cell C2 was set to =today()-B2 then copied and pasted in to all other rows for the C column 1. Click on the Cell which has to change color 2. Click Home 3. Click Styles 4. Click Conditional Formatting 5. Click New Rule 
6. Click Use a formula to determine which cells to format 7. Now enter your Formatting Rule under "Format values where this rule is true" 8. Click the Format Button and select the Font and Fill Color you require, in this case I used Font of White and Fill (Backgound) of Orange 
9. Click OK 10. Click Ok 11. Click OK 
12. You will now see the colors change based on the value of StartDate Try entering different start dates and the color changes based on the Diff value EXPLANATION In the Conditional Formatting the formula =TODAY()-B1>89 will only work for 1 row To make it work for each row substitute B1 for INDEX($A$1:$D$7,ROW(),2) rows() returns the current row number and 2 is Column 2 which gives =TODAY()-INDEX($A$1:$D$7,ROW(),2)>89 IP | ||||
|
||||