Conditional Formatting Troubles

[giacomo]

I am using the following conditional format to bold certain cities that match to 4 specific cities:

=MATCH(A1,CityList,0)

where CityList is a named range that refers to cells $D$3:$D$6, the data contained in those cells are the following 4 city office names:

Bakersfield
Tucson
SonomaMarin
Austin

This is working great with the exception of SonomaMarin, it bolds the other cities but not this one.

I'm using excel 97 on windows NT.

Thanks for your help,
giacomo

 

[Aladin Akyurek]

On 2002-04-29 14:55, giacomo wrote:

On 2002-04-29 14:43, Aladin Akyurek wrote:

On 2002-04-29 14:35, giacomo wrote:
Aladin - thanks for your help, I guess I can live without the highlighted rows /board/images/smiles/icon_frown.gif

Chris - my range is larger than a single printed sheet, A1:A197. But I'd like to hear your solution anyway, you've got me curious.

thanks
giacomo

Try the following, which I didn't test thoroughly:

Condition 1:

=MOD(ROW(),2)*(LEN(B:B)=0)

Format for background and font.

Condition 2:

=MOD(ROW(),2)*(MATCH(B1,CityList,0))

Format for background and font.

Condition 3:

=MATCH(B1,CityList,0)

Format just for font.

Aladin

nope, didn't work. I think I see where you were going with that though. I can make it work if only one of the 3 conditions evaluates to true, and the other two are false. right?

thanks,
giacomo

Yes, that's exactly what we need.

Change Condition 1 of the previous set to:

=MOD(ROW(),2)*(ISNA(MATCH(B1,CityList,0)))

Format for background (and for Regular Font which seems necessary).

Aladin

 

[giacomo]

Aladin,
Thanks for your help, I got it working. Here's what I ended up with…

Condition 1:
=MOD(ROW(),2)*MATCH(A1,CityList,0)
Formatted for background and font

Condition 2:
=MATCH(A1,CityList,0)
Formatted for text only

Condition 3:
=MOD(ROW(),2)
formatted for background only

Note: My cell that contains SonomaMarin evaluates to true for all three conditions but only the formatting from condition #1 is applied, #2 and #3 are ignored...just like you said. This is exactly the result I needed.

Thanks to everyone for your help!
giacomo

 

[Aladin Akyurek]

On 2002-04-30 09:29, giacomo wrote:
Aladin,
Thanks for your help, I got it working. Here's what I ended up with…

Condition 1:
=MOD(ROW(),2)*MATCH(A1,CityList,0)
Formatted for background and font

Condition 2:
=MATCH(A1,CityList,0)
Formatted for text only

Condition 3:
=MOD(ROW(),2)
formatted for background only

Note: My cell that contains SonomaMarin evaluates to true for all three conditions but only the formatting from condition #1 is applied, #2 and #3 are ignored...just like you said. This is exactly the result I needed.

Thanks to everyone for your help!
giacomo

Yeah, that's much shorter than what I cooked up.

Congrats.

Aladin

 

[inarbeth]

Not really an answer to this thread but I run a macro to change all three conditions on my sheet (or the relevant area) initially to =MOD(ROW(),2) as a default formatting. If I later want to apply other conditional formatting, I do not have to type the =MOD(ROW(),2) again. A useful timesaver.