Conditional Formatting Rule returning true but format not applied

**silverCORE** · 02-13-2011, 12:39 PM

--- Edit about answer
As martindwilson indicated, no INDIRECT usage was needed. scroll below for the solution.
---

Hi.

I'm no Excel expert, and I've been working the past three days in completing a conditional formatting formula with 2 conditions. Finally got it working, and the evaluation is TRUE, but the conditional formatting I specified is not being applied.

I've uploaded a new excel file with 3 samples of my records, no macros or anything, in case someone prefers to look at the implementation rather than read the full explanation.

http://share1t.com/3ovxm8

My scenario is simple. I have rows of Tasks (Task Name, Expected Finish Date, Date Closed).

I just want to highlight the rows that dont have a value for "Date Closed", and for which the Expected Finish Date has already passed.

The formula I came up with is this:

=AND(INDIRECT("D"&ROW())="", DATEVALUE(TEXT(TODAY(), "MM/DD/YYYY")) > INDIRECT("C"&ROW()))

where the D row contains the Closed Date and the rest is a comparison of date values, since INDIRECT("C"&ROW()) which is an evaluation of the Expected Finish Date returns a numeric value, I had to find how to convert the value returned for TODAY() to that numeric format.

Like i said, both conditions are returning TRUE, and in the worksheet i created two columns with the evaluation of each condition, both also stating true, however, my conditional formatting (a very basic Row Fill) is not being applied.

Can anyone please help?

Thanks

**martindwilson** · 02-13-2011, 12:50 PM

why all the indirect?
select a3 :J100 or whatever and cf as
=AND($D3="",TODAY()>$C3,$C3<>"")

**silverCORE** · 02-13-2011, 12:56 PM

Originally Posted by martindwilson

why all the indirect?
select a3 :J100 or whatever and cf as
=AND($D3="",TODAY()>$C3,$C3<>"")

martin, the reason i use indirect is because i plan to use that conditional formatting for a lot of rows, and i dont want to have to manually edit the conditional formatting of each.

if i use indirect, i can just Format-Painter one row and just apply it to others, or, are you saying that the cell references would shift automatically?

**silverCORE** · 02-13-2011, 12:58 PM

Originally Posted by silverCORE

martin, the reason i use indirect is because i plan to use that conditional formatting for a lot of rows, and i dont want to have to manually edit the conditional formatting of each.

if i use indirect, i can just Format-Painter one row and just apply it to others, or, are you saying that the cell references would shift automatically?

just tried it. although the formatting using specific cell references work, copying the format to use in other rows won't automatically change to reference those rows specific cells.

any ideas??

**martindwilson** · 02-13-2011, 12:59 PM

did you not try what i suggested? it definitely works that's what the $ are for

**silverCORE** · 02-13-2011, 01:01 PM

Originally Posted by martindwilson

why all the indirect?
select a3 :J100 or whatever and cf as
=AND($D3="",TODAY()>$C3,$C3<>"")

Correction.

It worked martin...sorry, i was using $D$3, instead of $D3. Told you i'm very bad with excel, but THANKS A LOT.

really appreciate the fast response and will definitely recommend this forum on my blog.