Results 1 to 3 of 3

Thread: Conditional Formatting on Today.

  1. #1

    Conditional Formatting on Today.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Good Morning,
    I am been driven mad by something that should be oh so simple, hope somebody can assist!

    I have a spreadsheet which contains a date field in Column "E".
    What I want to do is change the colour of the whole row if the date in column "E" is either Today, or a past date.

    In the conditional formatting option I have set the following:

    Formula Is =INDIRECT("E"&ROW())<=TODAY()

    This does work, well to a fashion, as if the cell in column E is blank, it is also taking the formatting rules...

    How can I get it to ignore blank cells?????


    Thanks!

  2. #2
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,336
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hello
    after selecting the entire range (say A1 to ZZ5000 or whatever is needed) use the following formula for the CF =AND($E1<=today(),$E1<>"").

  3. #3
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    The important part of Pecoflyer's solution is the correlation between what is the active cell and the range reference passed in your formula. If you select A1 and then select the rest of your area (whatever it may be, A1:Z1000, whatever), you should see A1 in the name box (directly left of the formula bar). So referencing $E1 would be appropriate. In that instance the column doesn't matter because it's locked in, but the row is relative, so that means it's relative to what the active cell currently is. Something to keep in mind.

    Assuming A1 is the active cell (at least row 1 is), you could use a custom formula like this...

    Code:
    =(LEN($E1)>0)*($E1<=TODAY())
    Edit: formula is only another alternative, Pecoflyer's would work just as well.

    HTH
    Regards,
    Zack Barresse

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •