Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How do I create the following conditonal formatting:

If the date in G3 is older than today, turn all of row 3 red

So in other words I want my entire row to be formatted based on the value in the relevant Date column

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Select the whole table or range, add a new rule and select Use a formula... Then input this as the formula:

=$G3<TODAY()

For more info, if you or anyone else doesn't know:

Using the $ fixes the cell reference. $G3 would allow you move the row reference but would fix the column reference, G$3 would do the opposite and $G$3 fixes it completely.

By setting the reference as $G3, you can drag the formatting down and it will work for each row, but if you drag it accross it will always look at your date column.

You can quickly change the cell reference fixing by selecting the cell reference within your formula and hitting F4.

TODAY ends in () because it is a formula with no parameters.

A final note

If you haven't already, I would recommend adding another conditional format that formats the row orange if =$G3=TODAY(), so that users can easily identify rows about to turn red.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.