Format row on change in A:A

    Hi all -


    Using condi-format to format rows when change in unit number in ColA
    However, my formula is highlighting first instance. I would like the second
    highlighted.


    Ex :


    A2 5890001 [currently highlight]
    A3 5890002 [this is the row I would like highlighted]


    Here's my formula in condi-format
    =IF($A2<>$A3, TRUE, FALSE)


    I tried flipping the 3 and 2. - no help


    Thanks
    -marc

    Re: Format row on change in A:A


    Mark, can you please clarify what you want here as when I look at your formula it will always be true (don't understand why but try it yourself via the fx button). Could hopefully then advise suitable formula?

    ________________________________________


    Good Luck!


    Ray :nana:

    Re: Format row on change in A:A


    Sorry about that Ray -


    My example was poor


    The repitition pattern is every 6 rows (approx)
    123001 [no fill]
    123001 [no fill]
    123001 [no fill]
    123001 [no fill]
    123001 [no fill]
    123001 [this row is currently highlighted]
    123002 [this row should be highlighted]
    123002
    123002
    123002



    Of course, we want to write the formula to handle whatever we throw at it
    The repetion pattern may not always hold true


    Thanks!
    -marc

    Re: Format row on change in A:A


    Mark,


    Try the following: =IF(($A2<>$A1), TRUE, FALSE) and copy CF down the column as needed.


    Cheers,
    DEF98

    Re: Format row on change in A:A


    Thanks Def -


    Same result
    I had tried your suggestion earlier, but tried again anyway.


    Thanks
    -marc

    Re: Format row on change in A:A


    Mark, if you place def98's formula starting in row 2 of the data then fill down this should work...it won't work if placed in the first row? I'm sure someone else could explain why but it's beyond me!

    ________________________________________


    Good Luck!


    Ray :nana:

    Re: Format row on change in A:A


    Markc,


    Sorry this is not working for you, sure seems it shouldn't be too difficult. The first row worked for me but the default reference is to $A65536. Therefore, if A1 is populated, it will always be shaded if A65536 is empty. You can change this to <>0 but the effect is almost identical. The first empty cell (at bottom of entered data) is shaded also since it is not equal to the last populated cell.


    I assume you are not doing anything to lose/overwrite the CF during your data entry, right?


    I have attached a small example FYI. Mark, perhaps you could post an example of your problem worksheet for us to work on.

    Re: Format row on change in A:A


    Mark,


    Try the attached revised sample file. Your CF formula "=IF($A2<>$A3, TRUE, FALSE)" was referencing the cell below, so when the sequence/value changed it was shading the one above. With the CF referencing the cell above "=IF($A3<>$A2, TRUE, FALSE)", the cell/row where the change occurs is highlighted. I know that you mentioned you tried switching the 2 & 3 previously without success, not sure what happened but this worked for me.

    Re: Format row on change in A:A


    Thanks Def -


    Wonder what I did wrong?
    Maybe because I started with 3,2 instead of 2,1


    Thanks for your help
    -marc

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login