Change row color,  when a column cell value changes from the previous row, Excel 2010

Posted on 2012-09-21
Last Modified: 2012-09-25
How do I :
Change the row color,
when a particular column cell value changes,
from the previous row,
in Excel 2010

For example, I would want the row color to change when 'a' changes to 'b', and 'b' to 'c'.

a 1
a 2
a 3
b 3
b 3
c  1
Question by:AndyPandy
    LVL 12

    Expert Comment

    Maybe conditional format could help. I don't get it exactly what you need, but if you need to change the color based in the value of the cell, this is what you need.
    LVL 22

    Expert Comment

    Use conditional formatting. If your data is in column A, in A2 go to conditional formatting and select New Rule - Use formula to determine which cells to format. Enter this format:


    Now copy A2, select the rows below you want formatted, right-click and select paste special. Select Formats and then OK.

    LVL 22

    Expert Comment

    Here's a sample

    Author Comment

    Further clarification of what I need.
    when a columnar cell value changes,
    I need:
    the changed row to go to a contrasting color,
    And all subsequent rows to stay that color,  
    until the columnar cell value changes again.
    So =c3<>c2 in conditional formatting, works,   but only once.

    So that the rows 'A' would have same color,
    'B' would change,  but stay the same as long as 'B" value held,
    'C'  would chang again.
    LVL 22

    Accepted Solution

    Here's how you can do that. Use an empty column. I'm using B for this. in B2 paste in the following:

    =MOD(IF(ROW()=2,0,IF(LEFT(A2,1)=LEFT(A1,1),$B1, $B1+1)), 2)

    Copy that in B2 and paste down. You'll find that every time the value changes in column A, B will change from 0 to 1 and back. Now in A2 set your conditional format formula to =$B2=1. Copy that and paste it down using paste special - format. See attached.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now