Thanks:  0
Likes:  0

1. 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

2. 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?

3. 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

4. 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.

[ This Message was edited by: aladin akyurek on 2002-04-29 14:30 ]

5. 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"

6. 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

7. 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.

8. 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....

9. 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.

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

10. 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

## User Tag List

#### Posting Permissions

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