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

 

[giacomo]

condition 1 is the Match formula
condition 2 is =MOD(ROW(),2)

I said in my post earlier that I was highlighting even numbered rows, I meant odd numbered ones.

thanks to everyone for your help on this.
giacomo

 

[Aladin Akyurek]

On 2002-04-29 12:08, giacomo wrote:
condition 1 is the Match formula
condition 2 is =MOD(ROW(),2)

I said in my post earlier that I was highlighting even numbered rows, I meant odd numbered ones.

thanks to everyone for your help on this.
giacomo

What do you want to happen if a city that must be hilited is in an odd numbered row which has to be hilited too?

 

[giacomo]

On 2002-04-29 12:23, Aladin Akyurek wrote:

On 2002-04-29 12:08, giacomo wrote:
condition 1 is the Match formula
condition 2 is =MOD(ROW(),2)

I said in my post earlier that I was highlighting even numbered rows, I meant odd numbered ones.

thanks to everyone for your help on this.
giacomo

What do you want to happen if a city that must be hilited is in an odd numbered row which has to be hilited too?

if it's on an odd numbered row I want to highlight it by changing the background color. If it's a matching city I want to bold the text.

seems like it should be easy but I can't make the two work together.

thanks.
giacomo

 

[Aladin Akyurek]

On 2002-04-29 12:30, giacomo wrote:

On 2002-04-29 12:23, Aladin Akyurek wrote:

On 2002-04-29 12:08, giacomo wrote:
condition 1 is the Match formula
condition 2 is =MOD(ROW(),2)

I said in my post earlier that I was highlighting even numbered rows, I meant odd numbered ones.

thanks to everyone for your help on this.
giacomo

What do you want to happen if a city that must be hilited is in an odd numbered row which has to be hilited too?

if it's on an odd numbered row I want to highlight it by changing the background color. If it's a matching city I want to bold the text.

seems like it should be easy but I can't make the two work together.

thanks.
giacomo

I believe we're stuck on this: that's what is mentioned in the Excel Help file regarding conditional_formatting:

Using multiple conditions If more than one specified condition is true, Excel applies only the formats of the first true condition.

This means that (a) MOD is already true (>0), when (b) MATCH is also true (>0). So (a) is applied. Changing the order doesn't help.

Aladin
This message was edited by aladin akyurek on 2002-04-29 14:30

 

[Chris Davison]

Giacomo,

how large is the entire data range you want to apply *both* of your conditional formats to ?

(ie A1 to D20........ small range

A1 to Z3000....... large range)

I can think of a "cheat" workaround, but it would only work for a range that is about as big as a normal single print page area....

it would also only be "viewable" as oppose to being "linkable"

 

[giacomo]

Aladin - thanks for your help, I guess I can live without the highlighted rows

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

 

[Aladin Akyurek]

On 2002-04-29 14:35, giacomo wrote:
Aladin - thanks for your help, I guess I can live without the highlighted rows

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

 

[Chris Davison]

Basically, it involves setting up an extra sheet, linked to your data

your original sheet has your first conditional formatting applied (MOD(row( etc etc)

the second has your second conditional formatting applied (bold text)

then you take a snapshot of the 2nd sheet using the camera option

view
toolbars
customise
commands
tools.......... scrolldown the options

click the camera icon and drag it to your toolbar, you should now have a camera sitting on your toolbar

now highlight your 2nd sheet of cells in question and click the camera, this will take a snapshot of those cells

now go to your first sheet, top left and click and drag to max size, overlapping the two identically, removing any border and making it see-through so the formats filtrate each other ( right click the image and select format picture)

if formatted correctly, you should still have your alternate format showing through, with bolded text sitting ontop

the camera image, however, is only as large as the print page size, so won't work with 180 lines

maybe you could do 4 sets of 90 lines....

told you it was fiddly !

these are also images, so don't sit as useable cells which you can link to affectively.....

food for thought though if you're desperate....

 

[giacomo]

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

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

 

[giacomo]

Chris thanks for your ideas too. I'm off work in about 5 minutes but I'll be sure to try your idea tomorrow.

thanks,
giacomo