Highlight Value Differences in Multiple Columns

Greetings All-
I was hoping to find a way to do a conditional format to highlight differences in rows. I’ll try to explain.   I have an excel file with dozens of rows and hundreds of columns.  I want to highlight the first time the value changes in each column. In the attached file, cells F4 and M3 would be the only ones highlighted.  I haven’t found a way to do this through ‘Find & Select -> Go to Special’ either with Row Differences or Column Differences.  I also haven’t found a way to do this via conditional formatting because when I use format painter to apply the format to the entire table, I get crazy results as the copy/pasted format is pointing at unrelated cells. My preference would be to highlight the cell, but I would welcome any solution which mitigates my having to visually scan each column/row to identify changes.  Any help is appreciated.  Thanks in advance!
Jeff VolkhammerConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Highlight B3:M5 (ie exclude first row) and use Conditional Formatting with this formula:


Cell references will be dynamic except for the first reference to row 1. This counts the number of occurences of the cell value in the range above it. If the Count is zero then it is the first occurrence of that value, ie it has changed.

This adds B4 to the list of highlighted cells as well as it has changed from the cell above.

Rob H
Patrick MatthewsCommented:
In the attached file, cells F4 and M3 would be the only ones highlighted.

Shouldn't B4 be highlighted as well?  If not, why not?
Patrick MatthewsCommented:
Rob, I tested that against the sample file, and it ends up highlighting all of Row 3 as well...
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Patrick MatthewsCommented:
Small adjustment to Rob's formula to make it work:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Patrick raised the correct point.
Are you entering the values like "Y" and "N" only or this is just to demonstrate that data has been changed?

I think you need to explain it a bit more.

Though as per your sample workbook and data in it, try this........

Select the range B3:M5 and use the following formula to make a New Rule for conditional formatting and set the format as per your choice.


For details refer the attached workbook.
Jeff VolkhammerConsultantAuthor Commented:
Thanks.  I think this formula is working perfectly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.