how to keep conditional formatting when adding row in middle of range
 

I have a list of data where i am formatting the rows based on the previous row to make it easier readable (basically if the data in the row above is the same it will dim the text in the row via a formula like $a3=$a2 for a range that starts in a3). However when people are entering or changing data they will want to enter rows in the list. When you do that the conditional formatting breaks the range where the conditional formatting applies and it basically skips the rows entered (i.e. if i enter a new row 17, now row 18 will check row 16 to do the conditional formatting). I tried using named ranges when setting the conditional formatting but this does not help since excel reverts to the cell references when applying.

Does anybody have any idea how to avoid this outside only allowing adding rows via a macro so that i can re-enter the cond formatting every time a row is added?

Thanks
Mischa

Ok, i just found a solution on a MR Ozgrid thanks to Batman..

He suggested using the INDIRECT function in the conditional formatting formula, so i tried

=INDIRECT("A"&ROW())=INDIRECT("A"&ROW()-1)

tried to enter a new row in the range and surprise, surprise it worked perfectly ;-)

Mischa

How about something like the below formula in the conditional formatting?

=INDIRECT("A"&ROW())=INDIRECT("A"&ROW()-1)


EDIT: Gah! Beat me to it! :)

Thanks anyway for replying Spencer..

It seems i was also celebrating too early.. This works indeed for column A but for column B i am looking at both column A and column B and although the formula works in excel (i.e. i get a true or false when needed) this formula always gives a false in conditional formatting... ??????

=AND(+INDIRECT("A"&ROW())=INDIRECT("A"&ROW()-1);+INDIRECT("B"&ROW())=INDIRECT("B"&ROW()-1))

is there a limitation in the amount of nesting you can do in conditional formatting?

Mischa

ohh and by the way, when i was just using the following the conditional formatting worked but as said before not when adding a row.. AARGHHH

=AND($a3=$a2;$b3=$b2)

Does it work if you take the two + symbols out of the formula?

No.. i did not have them originally but I added them when it did not work..

I've been trying this myself, also with no luck.
It's very odd because looking at the formula there is nothing wrong with it and I can see no reason why it doesn't work.

I shall keep investigating and will let you know what I find.
I will NOT let this beat me ;)

Thanks, highly appreciated, it is driving me up the wall ;-)

Try this in column B:

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

I wish I could say I solved the problem for you, but alas I sought help elsewhere and got the answer far quicker than I'd have worked it out.

It's not what you know, it's who you know that knows far more than you know! ;)

Great - that works perfectly. Thanks a lot for the effort!!!

In the mean time i had also figured out another way around it, namely using max 1 indirect comparison per condition and applying to more than one column:

=+NOT(INDIRECT("A"&ROW())=INDIRECT("A"&ROW()-1)) applied to column A, B and further columns
=+NOT(INDIRECT("B"&ROW())=INDIRECT("B"&ROW()-1)) aplied to column B and further columns
etc

This also works as long as you make the text is basically already in the dimmed format (it will only come in view when the autoformat is met, i.e. when it is different vs. the row above or if the columns before in the same row are different vs. their row before).

Thanks again!
Mischa