Change Row color based on change to a value in a Column

See example below.  Using Excel 2010 and normally go through a report and change row color when value changes.  You can see here where row color is constant until User value changes and then switches to grey and then back to white with next User change.  Very useful in sending out to group and allowing individuals to know where there information starts and stops within a report.


Excel 'Format as Table' actually has similar formating but changes colors for every row rather than at value difference.  Is there functionality I am missing to do color change with values or do I need to create VBA script to do this? (In that case, the solution would probably be more labor intensive than the problem).


Thanks for your help!



Year User Spend
2013 Joe  $                                     45.00
2013 Joe  $                                     34.00
2013 Martha  $                               7,878.00
2013 Gary  $                                   554.00
2013 Gary  $                                   534.00
2013 Gary  $                               5,546.00
2013 Barbara  $                                     55.00
2013 Garth  $                                       5.00
2013 Garth  $                                       5.00
2013 Garth  $                                       5.00
2013 Garth  $                                     45.00
2013 Joe  $                                     55.00
2013 Joe  $                                   144.00
2013 Joe  $                                   933.00


Question Info

Last updated June 18, 2019 Views 9,125 Applies to:



Since Joe is not sandwiched between different names, why should that be colored?  Shouldn't only Martha and Barbara be coloured?


1. Suppose your data is in range A1:C15 (headings are in row 1)

2. While on cell A3, go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to format

3. In the formula bar there, enter this formula




4. Click on Format and select your desires colour

5. Copy this cell and Paste Special > Format to all cells


Hope this helps.


Ashish Mathur

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.