Conditional Formatting with different sheets (Matching data/lookup)

Hi again,

Here's my problem:

I have 3 sheets. And in the sheet #2 is where I have the conditions. The only problem is that I have 2 conditions that do the same lookup: Condition 1 and 3.

Condition 1: Makes the text bold and colors the background cell to orange.
(from Sheet 3)


Condition 3: I want the text to become Italic and underline. But it seems that condition 1 has priority and this condition doesn't do nothing.
(from Sheet 1)

Both conditions from different sheets lookup the same information.

Any help please?


Thanks
Elegidos (Spain)

That's how conditional formatting works in Excel 2003. Only 1 conditional format is ever applied, the first that's satisfied.

In some circumstances you can achieve what you want by combining the formats, and tests, in one condition .......but in this case you may not have enough conditions...what does condition 2 do?

For more specific help you might need to specify the exact conditions......

Ok, I've attached an example of what I want to do.

It has 3 sheets, sheet 1 with the 2 conditions I want. Sheet 2 and 3 are the ones where they are being looked up/match up.


Condition 1: Makes the text bold and colored black.

Condition 2: Makes the text italic and colored orange.

In your example Range2 is a subset of Range1 so you can never have a number that's in the former without being in the latter.....but assuming that might be the case you can set it up so that condition 1 checks whether the number is in both ranges, i.e.

=AND(COUNTIF(range,A1),COUNTIF(range2,A1))

and then condition2 and 3 would be the same as your current 2 conditions.

For the formatting you can't have both black and orange font, obviously but you could have formats that can co-exist, e.g. if condition 2 is black font and bold and condition 3 is yellow background and italic then condition 1 can be set to all of those, i.e. black font, bold italic and orange background