Excel Change Cell Color on Condition

Search for :

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

Previous Page    Next Page    IP

_________
