Compare rows?

[sandernoteborn]

Hello,

Can someone help me with creating a formula to compare some rows.

Let's say I've got a few cell behind eachtother with values. (A1,B1,C1). I created a macro which copy's these values to the cells behind these three. So cell D1 has the same value as A1, and E1 like B1 and F1 like C1. Every time I run de macro it will copy the new values. Now I would like to create a formula who compare's A1 with D1 and sofurther. That in case I change the value of A1 excel gives me a sign that A1 is not equal to D1.

I tried to create a Conditional format in another cell because I want a cell to get colored when there's a change made... But if I select the cells it only works when I change the first cell (A1 or D1). But it doesn't color when I change B1 or C1. I just want to see if there's a change made in the complete first ROW!

Can somebody tell me how to do because my de following conditional format won't work:

A1:C1<>D1:F1

Thanks!

[Vito]

Try this in the Conditional Formatting after selecting D1:F1

=OR($D1<>$A1,$E1<>$B1,$F1<>$C1)

[sandernoteborn]

Thanks but no!, it won't

Maybe I didn't make it totally clear...

Let's say these are my simple values:

B1 = 1
C1 = 2
D1 = 3

E1 = 1
F1 = 2
G1 = 3

I want cell A1 to recognize a change between B1 and E1, C1 and F1, D1 and G1.

So if these are the new values:

B1 = 2
C1 = 2
D1 = 3

E1 = 1
F1 = 2
G1 = 3

I want cell A1 to change color or anything...
But when I use Conditional format on cell A1 and use this formula:
B1:D1<>E1:G1 it only works when I change B1 or E1. The rest doesn't work! How can I get this working?

Thanks!!

[sandernoteborn]

I think I have to use an IF but I don't know how!

Maybe I just can use an IF formula in cell A1 and compare them seperated from eachother!

B1 - E1
C1 - F1
D1 - G1

But how do I create that formula?

Thanks

[hans bal(nl)]

In A1 enter =AND(B1=E1;C1=F1;D1=G1)

This will return a value TRUE if B1 equals E1, C1 equals F1 and D1 equals
G1. and FALSE idf any of these three comparisons fail. You can base your
conditional formatting based on the True or False value.

( depending on your regional settings you may need to replace the ; in the
formula with a comma , )


Does this help ?

"sandernoteborn" wrote:

>
> I think I have to use an IF but I don't know how!
>
> Maybe I just can use an IF formula in cell A1 and compare them
> seperated from eachother!
>
> B1 - E1
> C1 - F1
> D1 - G1
>
> But how do I create that formula?
>
> Thanks
>
>
> --
> sandernoteborn
> ------------------------------------------------------------------------
> sandernoteborn's Profile: http://www.excelforum.com/member.php...o&userid=34225
> View this thread: http://www.excelforum.com/showthread...hreadid=539815
>
>

[sandernoteborn]

Sadly it doesn't

[Vito]

Did you try this in the conditional format after select A1,

=OR(B1<>E1,C1<>F1,D1<>G1)