Excel is a very handy tool for working with data. It has many built-in features like conditional formatting which help in
formatting and highlighting the data
based on certain rules. The rules can be based on number values or ranges, text values or using dates as well. For formatting using dates, one has to select the conditional formatting
. Then one has to create a new rule using
date criteria and the relevant colors
to highlight different rows. All these options make Excel a great tool for
You're trying to change the color of an entire row to 3 different states depending on the result.
The row needs to stay clear when the date in a particular cell (let's say B2) is after TODAY.
I want the row to change to orange when the date in a particular cell (let's say B2) is Today but only if another cell (let's say C2) is blank.
If the other cell (still C2) isn't blank then I want it to stay clear.
The row needs to change to red when the date in a particular cell (let's say B2) is before TODAY but only if another cell (let's say C2) is blank.
If the other cell (still C2) isn't blank then I want it to stay clear. Solution
Highlight a row you wish to format (or highlight all data on that row only)
Select 'conditional formatting' from the ribbon menu
For the first one you will need to select the cell that the date is in that you wish to action
In my case I am using A2 and B2
Create the following rules (x3 in any order)
=$B$2>TODAY() Then format this any color you like. This means when the date in cell B2 is later than today, format the line with the color selected.
=$B$2<TODAY() Then format the color required. This means when the date is before today's date, then format the row with the color selected.
=$B$2 = TODAY() Then, again format the color desired. This means that when the date in cell B2 is today's date the color selected color
Then create the next rule as follows:
=$A$2="" Then select the clear/white for the color. This means that if cell A2 is empty then color the row clear/white. You must tick the stop if true box at the end of rule.
What this will do is if cell A2 is blank, then the rest of the rules are ignored. Note that
for this tip on the forum.