VBA conditional formatting with if's, etc.

[yjbyjb]

I've been struggling with this one...I've been referred to this forum which I have heard has the friendliest most helpful people around.

I present the challenge below:

I attached the spreadsheet to make things easy.

What I'm looking to accomplish:

When the number in Columns B and D are above the "Goal" number in row 8 for each individual column...highlight in yellow.

BUT only if the letters in columns C and E are not "U, UJ, NJ, JN, or R"
Only highlight numbers that are attached to no letter at all or a J.

Cheers to anyone who can help me with this!!

I appreciate it and thank you in advance

P.S. Oh I'm on Excel 2000 (yes I know...my company is 8 years behind)

[Kenneth Hobs]

That is alot of AND conditions.

Step 1. Name B8 as Goal1. Name D8 as Goal2. Add the exception list and name the range ExList.

Step 2. Block select A2:E7, Format > Conditional Formatting...
Formula Is
=AND($B2>=Goal1,$D2>=Goal2,COUNTIF(ExList,$C2)=0,COUNTIF(ExList,$E2)=0)=TRU E
a. Set the Format to Yellow.

[Bob Phillips]

That is alot of AND conditions.

Step 1. Name B8 as Goal1. Name D8 as Goal2. Add the exception list and name the range ExList.

Step 2. Block select A2:E7, Format > Conditional Formatting...
Formula Is
=AND($B2>=Goal1,$D2>=Goal2,COUNTIF(ExList,$C2)=0,COUNTIF(ExList,$E2)=0)=TRU E
a. Set the Format to Yellow.

You can do away with the =TRUE, the conditions will evaluate to TRUE or FALSE, so it is the equivalent of saying

=TRUE=TRUE

or

=TRUE=FALSE

[Kenneth Hobs]

What Bob said is "True" as usual.

Maybe Bob can tell us how to get around this but on my 2003 version, I have to select the range to get it to evaluate the conditional formula. I block select the sheet by clicking the block before A1 to select/unselect the whole sheet.

I would probably use VBA myself though.

[Bob Phillips]

Hey Kenneth,

I am not sure I follow what you are saying. Can you elaborate a bit?

[Kenneth Hobs]

When I changed a value in the ExList to make one row evaluate to true and format to yellow, then I had to block select to make the conditional format's formula evaluate.

I will have to test it at home and see if it does it there too.

[Bob Phillips]

No, I don't experience that. I changed the JN in ExList to X and the row that was yellow cleared down.

[Kenneth Hobs]

I wouldn't mention the problem but the OP or others may see the same thing.

Wish I knew why both my Excel 2003's do it, very odd. I am using sp3 on my home version on xp. At work, I use xppro.

[Bob Phillips]

I use XPPro as well, but I am only on XP2. I heard about XP3 doing some odd things, so have held back until I have checked it out.

[Kenneth Hobs]

Now I feel silly. I was thinking about this and thought maybe it was one of my settings since I have sp2 on the work computer. I had manual calculation mode set.

Weird though how manual calulation would allow an update in conditional formatting formula by the block selection of the cell(s).

[Bob Phillips]

ARe you sure that you didn't do an F2 to force a re-entry of the formula. That forces recalculation of tha (those) cells even with manual calculation.

[Kenneth Hobs]

No, I had tried F2 yesterday which is why I didn't think about the calculation mode until today.

F9 forces a recalculation which will work in manual calculation mode to do it.

[yjbyjb]

Thanks for your replies....
I'm trying to accomplish this using VBA...however it's still causing issues. I cannot get it to stop coloring all of the numbers even the ones that have the exceptions....

[Bob Phillips]

Post your workbook.