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:-
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
In the Conditional Formatting the formula
will only work for 1 row
To make it work for each row substitute B1 for
rows() returns the current row number
and 2 is Column 2
Previous Page Next Page IP