+ Reply to Thread
Results 1 to 11 of 11

Conditional formatting confusion

  1. #1
    Registered User
    Join Date
    04-01-2006
    Posts
    19

    Conditional formatting confusion

    Hi,

    I am trying to apply the following conditional formatting rule:

    =AND($I$1<TODAY(),ISBLANK($L$1))

    This should test if a value in column "I" is less than today's date AND if column "L" is not blank. If so, it should highlight the cell.

    However it highlights some cells where column "L" is not blank (on the corresponding row).

    Do I need to enter fixed cell references, as above? I cannot see how to enter a formula that will apply to all cells in column "I". I also do not understand why the formula doesn't work for all cells.

    Any help greatly welcomed!

    Thanks

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Conditional formatting confusion

    While selecting the data for applying CF the Activecell should be in 1st row.

    =AND($I1<TODAY(),$L1="")


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: Conditional formatting confusion

    If you highlight all the cells that you want this to apply to first, and use relative addresses, then Excel will automatically adjust the formula to apply to all the cells. So, with the cells in column I selected (and with cell I1 as the active cell), choose Conditional Formatting | New Rule | Use a Formula... and then put this formula in the dialogue box:

    =AND($I1<TODAY(),$L1<>"")

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    04-01-2006
    Posts
    19

    Re: Conditional formatting confusion

    Thanks for the replies unfortunately it's still randomly selecting cells to highlight, where the rule shouldn't apply.

    This is an example of the values I'm using, if it helps:

    Column I: =WORKDAY(J12,G12-1)
    Column L: 31/10/2014

    Thanks

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: Conditional formatting confusion

    Did you remove all the earlier conditional formats before adding the new rule?

    Pete

  6. #6
    Registered User
    Join Date
    04-01-2006
    Posts
    19

    Re: Conditional formatting confusion

    Yep I did mate thanks

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: Conditional formatting confusion

    Well, maybe you should attach a sample workbook that displays these problems (point them out to us).

    Pete

  8. #8
    Registered User
    Join Date
    04-01-2006
    Posts
    19

    Re: Conditional formatting confusion

    Ah, hold on, I don't think I did. It's working now.

    I'm using this:

    =AND($I1<TODAY(),$L1="")

    How can I make it not apply to all other blank cells? At the bottom of my data (after last row) all cells are highlighted in column "I".

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: Conditional formatting confusion

    I thought you wanted L1 to be not blank, according to your description in Post #1.

    Anyway, try this:

    =AND($I1>0,$I1<TODAY(),$L1="")

    If a cell is empty it will be less than today, so you need the extra condition shown in red.

    Hope this helps.

    Pete

  10. #10
    Registered User
    Join Date
    04-01-2006
    Posts
    19

    Re: Conditional formatting confusion

    Dude...you're a superstar. Good work, now go and have a good day! My mistake in first post.

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: Conditional formatting confusion

    Glad to help.

    If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. conditional average confusion
    By GillL in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-06-2014, 11:47 AM
  2. Replies: 4
    Last Post: 09-24-2012, 05:33 PM
  3. Conditional Confusion w/date functions
    By maloneyman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-08-2011, 02:57 AM
  4. Excel formatting creating confusion
    By cstanley9 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2009, 12:16 AM
  5. Confusion...(conditional formatting)
    By nishapurohit in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 01-31-2006, 05:40 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1