INDIRECT in Conditional Formatting Question

[Spencer101]

Hi,

I'm stumped as to why this doesn't work, but am sure it's something simple.

I have conditional formatting in A3 that compares A3 to A2 and if the results are the same it changes the cell colour. - =INDIRECT("a"&ROW())=INDIRECT("A"&ROW()-1)

I then have conditional formatting in B3 that should change the cell colour if A2 & A3 match and B2 & B3 match - =AND(INDIRECT("A"&ROW())=INDIRECT("A"&ROW()-1),INDIRECT("B"&ROW())=INDIRECT("B"&ROW()-1))

but for some reason this second one doesn't work.

For info, I'm using INDIRECT in this to maintain the conditional formatting as and when new rows are entered in the middle of the data.

Any one got any bright ideas on this?
Am I just missing some fundamental point?

Thanks.

S.

[Cutter]

For the first I'd use: =INDEX(A:A,ROW())=INDEX(A:A,ROW()-1)

[Cutter]

And this for the 2nd

=AND(INDEX(A:A,ROW())=INDEX(A:A,ROW()-1),INDEX(B:B,ROW())=INDEX(B:B,ROW()-1))

[Spencer101]

For the first I'd use: =INDEX(A:A,ROW())=INDEX(A:A,ROW()-1)

Thanks for the reply Cutter. I thought of that but the formatting will continue down the sheet so if row 4 = row 3 the conditional formatting will kick in and if row 5 = row 4 it will again, etc. etc. etc.

Sorry, I know I should have explained that at the start.

[Spencer101]

Ignore my previous reply. A combination of my first formula and your second one works wonders!

Many thanks Cutter

[Cutter]

The formulas are working for me

[Cutter]

Oops, should have refreshed.

You're welcome. Glad you got it working. I always avoid INDIRECT() whenever possible.

[Spencer101]

You're a star. Can't give rep on this occasion as apparently I need to "share the love" before I can leave more for you... But I would if I could.

[Cutter]

Hmmm, that's weird. Is it because I was the last person you repped?

Anyway, it's the thought that counts. Thanks.

[Cutter]

Ahh, I see you repped me on Jun 17. Maybe there's a time limit. Or you can't rep the same person consecutively. Dunno.

[Spencer101]

I think so. I'm usually trying to answer questions rather than pose questions so don't have much call to rep people. I shall remember that I owe you some and will add when I can