colouring of rows if.......

[tonyvee1973]

Hi All
Hopefully a quick one for someone but driving me nuts!
I have an excel table that I need to compare a row against the row above it. If the row matches exactly I need to colour BOTH rows GREEN
If the values in A, B and C match but NOT D then I need to colour both lines in RED.
I have attached a small sheet to show what I need, hope someone can help. Its an active sheet so need a formula as I cant use conditional formatting.
Thank you

 

[SkipVought]

Hi,

Exactly what columns are you including in your match logic?

Can't understand why the reds are red and the uncolored red are uncolored.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tonyvee1973]

hi skip
if I leave a blank row 1 as a header row, I insert this formula into row 3 at the end of the data. It shows me TRUE or FALSE
if its true then I know the conditions I need match
=IF(A3=A2,B3=B2,D3=D2)

 

[SkipVought]

header row? Where??? Not in your uploaded example!

=IF([highlight #FCE94F]A3=A2,B3=B2,D3=D2[/highlight])

So if [highlight #FCE94F]THAT[/highlight] then WHAT? Else WHAT?

But that's only TWO results and you have THREE color results???

Need your other logic.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

So after I added a heading row, this will make the tow green rows TRUE and every other row FALSE.

In Row 2...
[tt]
=OR(AND(A3=A2,B3=B2,D3=D2),AND(A1=A2,B1=B2,D1=D2))
[/tt]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Your workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tonyvee1973]

sorry skip, don't think I can explain what I need properly.

If the values in A, B, C and D match the row directly above then I need to colour BOTH rows GREEN
If the values in A, B and C match the row directly above but D is different then I need to colour both lines in RED

does this make any sense at all? lol.

 

[SkipVought]

Your workbook with the above modifications
[tt]
GREEN: =AND($A1=$A2,$B1=$B2,$C1=$C2,$D1=$D2)
RED: =AND($A1=$A2,$B1=$B2,$C1=$C2,$D1<>$D2)
[/tt]

Of course, this is using the Conditional Formatting feature in Excel.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Oh, you did say BOTH...
[tt]
GREEN: =OR(AND($A1=$A2,$B1=$B2,$C1=$C2,$D1=$D2),AND($A3=$A2,$B3=$B2,$C3=$C2,$D3=$D2))
RED: =OR(AND($A1=$A2,$B1=$B2,$C1=$C2,$D1<>$D2),AND($A3=$A2,$B3=$B2,$C3=$C2,$D3<>$D2))
[/tt]


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tonyvee1973]

Thanks skip, works like a charm.
now I just need to work out how I get into the query that the excel sheet runs from
appreciate the help

 

[SkipVought]

Query?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}