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.

My problem is that I want to color in red a list Custom Formatted Dates when the date is overdue.

For instance, I got two actions:

  • Buy Bread : Must be finished before 27/01
  • Buy Milk : Must be finished before 25/03

My Excel looks like:

       A                B                C
1   Action            Date            Finished
2   Buy Bread         27/01           NO
3   Buy Milk          25/03           Yes

I want to have the Column B be Red if the date is outdated and the action NOT finished

I've used Conditional Formatting such as:

  • Range is : "= $B:$B"
  • Formula is : "If value in the cell is > TODAY()
  • The cell must be RED only IF the action is marked as NO
  • And, of course, Formatting is with the Red Color.

I can't use the "IF A3 is NO" condition properly in my conditionnal formatting formula. How can I put this IF with the "CURRENT CELL"?

For exemple:

IF ( CURRENTCELL in B2 > TODAY AND CURRENTCELL in C2 = NO )
share|improve this question

1 Answer 1

up vote 3 down vote accepted

In your conditional formatting window simply enter this formula:

=AND($B2<=TODAY(),$C2="No")

You may need to replace , with ; depending on your locality.


Note that conditional formatting works by checking a formula, and if the result is TRUE it applies the format. As a result you don't use IF statements, as these are used for applying a specified outcome if a condition is true.

An explanation of Conditional Formatting
The formula that you write is dynamic. Imagine you added a column at the end of your data, wrote the formula into the first cell and copied it down.

Because you didn't lock the row number, the formula changes to look at the row it is in. You may end up with 100 copies, each one looks at one of 100 rows.

This is the same, the range you selected is your column, and Excel modifies the conditional formatting to check each row separately. When it's checking row 6 the formula has changed to refer to B6.

This may be of help: chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics.

Edit
For future reference, modified for French: =ET($B2<=AUJOURDHUI();$C2="No")

share|improve this answer
    
Sorry, layout is false, I've corrected it. –  Xaltar Jan 28 at 10:28
    
Mh ... still not. I got "=ET($B:$B>AUJOURDHUI();$C:$C="No")" (i'm french). For range used : "=$B:$B". And it only makes my Excel laggy ... –  Xaltar Jan 28 at 10:47
    
Also, if you're using Excel 2010 (and don't need the workbook to work in 2003 or earlier) you should be using tables, and as such you can set the range for the formatting to the table column. It will then adjust when you add new rows to the table, which will reduce lag. As it is you're asking it to check around a million rows: office.microsoft.com/en-gb/excel-help/… –  CLockeWork Jan 28 at 11:08
    
Thanks ClockWork, this link is cool. I didn't know that. However, for the solution, I don't understand the $B2. This is only one cell right ? I need a whole range ... –  Xaltar Jan 28 at 11:56
    
Yes. But in your exemple, there is only a row. I need a range such as B2:B3 or B2:B10. –  Xaltar Jan 28 at 12:51

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.