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!
EE-Example-Table.xlsx
Aquilon7Asked:
Who is Participating?
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:

=COUNTIF(B$1:B1,B2)=0

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.

Thanks
Rob H
0
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?
0
Patrick MatthewsCommented:
Rob, I tested that against the sample file, and it ends up highlighting all of Row 3 as well...
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

=COUNTIF(B$2:B2,B3)=0
0

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.

=AND(B3="Y",COUNTIF(B$2:B3,"Y")=1)

For details refer the attached workbook.
EE-Example-Table.xlsx
0
Aquilon7Author Commented:
Thanks.  I think this formula is working perfectly.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.