How to highlight rows when cell value changes in Excel?
If there is a list of repeated values in your worksheet, and you need to highlight the rows based on column A which cell value changes as following screenshot shown. In fact, you can quickly and easily finish this job by using the Conditional Formatting feature.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial
To highlight the rows which value is different from above value based on a column, you can apply a simple formula mixed with the Conditional Formatting.
1. Select your data range that you want to use, if your data has headers, exclude them.
2. Then click Home > Conditional Formatting > New Rule, see screenshot:
3. In the New Formatting Rule dialog, click Use a formula to determine which cells to format, and then enter this formula =$A3<>$A2 (A3, A2 in the column which you want to identify the cell value is different from the above value.)into the Format values where this formula is true text box, see screenshot:
4. And then click Format button to open the Format Cells dialog, under the Fill tab, choose one color you like, see screenshot:
5. Then click OK > OK to close the dialogs, and the rows have been highlighted which cell value changes based on column A.
Note: Conditional Formatting tool is a dynamic function, if you change values in column A or insert new row between the data, the formatting will be adjusted as well.
Tip: If your data has no headers, please enter this formula =$A2<>$A1 into the Format values where this formula is true text box