Results 1 to 6 of 6

Thread: Conditional formatting not working

  1. #1

    Question Conditional formatting not working



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

    Hello,

    I have 3 conditional formatting rules, one of which isn't working. I've moved the rule in question to the top of the list and it still doesn't work. I have a column with dates in, which I compare against a predefined date elsewhere in the sheet. I'll explain below:

    If I do JUST this, it works:

    =INDIRECT("D"&ROW())<>""

    If I do JUST this, it also works:

    =INDIRECT("D"&ROW())

    But if I combine them together with the AND() function, it doesn't work:

    =AND(INDIRECT("D"&ROW())<>"",INDIRECT("D"&ROW())

    Can anyone help please?

    Many thanks,
    Steve

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    What is the =INDIRECT("D"&ROW()) supposed to actually be testing for?

    Incidentally, there is no need to use INDIRECT and ROW. If you have say A2 active when applying the formatting, using just =$D2 will achieve the same thing.
    Circumference of a circle = 2πrē



    ēthe circle's radius

  3. #3
    My test:
    If the date in INDIRECT("D"&ROW()) is after TODAY() then I want to change the background colour of that row.

    However, some rows won't actually have any data in INDIRECT("D"&ROW()) at all, in which case the background colour still changes, so I wanted to test to see if there was any data there first before applying the date comparison.

    I'm working on a range of rows, and when I tried using a single cell reference (even though the formula applied to the range, weird things started to happen!

  4. #4
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    Assuming A2 active again:
    =AND($D2<>"",$D2>TODAY())

    If that doesn't work, can you post a workbook?
    Circumference of a circle = 2πrē



    ēthe circle's radius

  5. #5
    Thanks for the reply - it still doesn't work. I've posted a workbook. The range to which the conditional formatting should be applied is called "LIST".
    Attached Files Attached Files

  6. #6
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    Since the range starts on row 7 the formula is:
    =AND($D7<TODAY(),$D7<>"")
    Circumference of a circle = 2πrē



    ēthe circle's radius

Posting Permissions

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