Highlighting Data IF...
 

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

Highlighting Data IF...
 

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" wrote in message
...
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

Highlighting Data IF...
 

On Apr 4, 7:18*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Thanks for your help, this is brilliant!!