Conditional Formatting Issue - MS Excel

I am trying to utilize conditional formatting logic  and for an unknown reason I cannot get it to function correctly on other spreadsheets and/or tabs.  

The new tab I added data to is entitled:  Testing

The conditional formatting rule that is working is on tab:  Q_2871141  (I received this logic from Fanpages a couple weeks ago)

I have copied the exact formula to an additional tab and to other spreadsheets and cannot determine why it won't work as it does on the 1st tab.  The data format on the new tab is the same as is on the tab that is working correctly.

My New tab - "Testing"
Tab that is working - "Q_2871141"

The goal of the formula is to compare rows with the same value in column B and highlight the differences for any subsequent rows, as it does on the 1st tab.

Any help to resolve my issue is very much appreciated.
Q_28711441b_TEST.xlsx
azaunAsked:
Who is Participating?

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

x
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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
it seems that the content in both sheets: Q_28711441 and Testing are different?

How these 2 sheets are related to each other? since the values of sheets are different, even we copy the conditional formatting from sheets: Q_28711441 to Testing, it may not work.
azaunAuthor Commented:
The data in the 2 tabs is different, however I would like the comparison to be the same - to compare subsequent rows with the same value in column B and highlight the differences for any subsequent rows, as it does on the 1st tab.

For example:
Row 2 & 3 should be compared because column B - "code", value = 24 is the same
Row 4 & 5 should be compared because column B - "code, value = 28 is the same

Hopefully that makes sense.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>> to compare subsequent rows with the same value in column B and highlight the differences for any subsequent rows
ok, so which column need to be highlighted if there's difference in Sheet: Testing?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

azaunAuthor Commented:
Using column B as the basis, it should compare any specified columns in the range inside the conditional formatting formula:   I believe in the "testing" tab it is as below:

"Testing" tab:
Applies To:  =$B$2:$AE$234
Formula:  =B2<>OFFSET(B2,MATCH(SUBSTITUTE(SUBSTITUTE($B2,"?","~?"),"*","~*"),$B:$B,0)-ROW(),0)

the number columns included can be different depending on the dataset in the tab.

For some reason the same Formula, with a different range works on the 1st tab - "Q_28711441"
Thanks!
Ejgil HedegaardCommented:
The problem on sheet Testing is that a lot of the cells have trailing spaces.
As example: B2 = "24", 2 characters , but B3 ="24                                 ", 35 characters
It looks the same, but is not.
The problem with the spaces are for every uneven row.
So when you compare there is never a match in column B.
Go to B3, press F2 and see where the marker is.
Remove all the spaces and the formula will work for row 3.
But not as expected, since same problem are in all the other columns, so to many cells are marked.

I think the conditional formula is much to complicated to see if 2 cells following each other are not equal, when the 2 cells in column B are equal.
This will do the same =AND($B2=$B1,B2<>B1)
I have removed all the spaces on sheet Testing, and changed the conditional formula to the above.
Q_28711441b_TEST-1.xlsx

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
azaunAuthor Commented:
Thanks!  The spaces corrected my issue - I am going to stay with my original formula however, since there can be more than 2 rows in the comparison.
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.