Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 906
  • Last Modified:

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

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
  • 3
1 Solution
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.

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.

Here's a sample
AndyPandyAuthor Commented:
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.
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now