Highlighting Data IF...

[NPell]

I had an answer to a query, and then added a 2nd question to it, but
it probably got confusing so i thought i could start again - this also
might help other people search for this aswell...

How do I highlight a row IF..
Using Cell A1 as an example..
a) There are duplicates of A1 in A:A (which are in order)
b) There is an "X" in column C within this range of Duplicates.
... I then want all the Rows with Duplicates in to be highlighted.

IE.

A B C
1 b
2 b
3 b X
4 b
5 c
6 c
7 c
8 d
9 d X
10 d

Rows 1-4 will be higlighted.. because they are duplicated in A, and
there is an X within that range on C..
Rows 5, 6 and 7 wont be highlighed.. because although they are
duplicated, there is no X..
Rows 8, 9 and 10 will be highlight for the same reason as 1-4.

If you can help that would be great.
xxxxxxxxxxxxx

 

[Bernie Deitrick]

NPell,

Select A1 to CXXXX, then choose Format / Conditional Formatting, "Formula Is" and use the formula

=SUMPRODUCT(($A$1:$A$XXXX=$A1)*($C$1:$C$XXXX="X"))<>0

Of course, replace the XXXX with the row number of the last filled row. Then choose your fill
format to be whatever color you want....

HTH,
Bernie
MS Excel MVP

 

[NPell]

NPell,

Select A1 to CXXXX, then choose Format / Conditional Formatting, "Formula Is" and use the formula

=SUMPRODUCT(($A$1:$A$XXXX=$A1)*($C$1:$C$XXXX="X"))<>0

Of course, replace the XXXX with the row number of the last filled row.  Then choose your fill
format to be whatever color you want....

HTH,
Bernie
MS Excel MVP









- Show quoted text -

Thanks for your help, this is brilliant!!