Exact comparison including bold / italics / underline

I have a variable number of words in each cell of two highlighted Columns. For each row I need to highlight the corresponding cells with either Green or Red depending on whether the two cells do or do not match the following criteria:

1. The text is exactly the same including upper- or lower-case
2. Intervening number of spaces between corresponding words in both cells is the same
3. Each corresponding word in the two cells has the same formatting for Italics/Bold/Underline (Color/Font do not matter)

I guess this will require some VB Code. Any help will be appreciated.

Thanks

I just put some code together for you to get a feeling what you can do.
Not the most beautiful, but it gets it done.....

Please Login or Register  to view this content.

I should add that my version does not solve your entire problem, it just tells you whether the two cells match. Personally I would use conditional formatting to highlight the cells based on a call to FORMATMATCH.


G e r a r d has a complete solution, although I would simplify the code as shown. First, note that comparison of two strings in VBA will always be case-sensitive, unlike comparison in an Excel sheet. Therefore there is no need for a character-by-character comparison. Also the logic can be simplified to be similar to the function I provided above. Note that I have assumed that the data starts in column A, and you are comparing to the next column to the right. That may need a little adjustment if your situation is different.

Please Login or Register  to view this content.

I am surely missing something here because I am getting TRUE even if A1 contains capital A and B1 contains capital A Bold and/or italicized and/or Underscored. Please see attached file. Thanks.

Range("A1")???
Remove that

Please Login or Register  to view this content.

Range("A1") returns the upper left cell of the parent. This is added in case the user passes in a range with more than one cell. I tested that code successfully before posting.

Originally Posted by qjawaid

I am surely missing something here because I am getting TRUE even if A1 contains capital A and B1 contains capital A Bold and/or italicized and/or Underscored. Please see attached file.

That is not what I'm seeing when I open your file. Everything looks correct to me. Normally I don't post images but here is what I see.

Originally Posted by qjawaid

This is strange!Could it be that you might be using Excel 2007 while mine is 2003? I assume Gerard is seeing what I see. Can Gerard please confirm?

Gerrad's change of removing Range("A1") solved the original issue however now if the contents are mixed italics/non italics I get a #VALUE error!

The code works for me.
Seeing the same as 6StrinJazzer.
Gerard

Originally Posted by qjawaid

Could it be that you might be using Excel 2007 while mine is 2003?

I suppose that is possible but that is usually an issue only with built-in functions, not VBA. My VBA is not using any unusual functions or features that were not available in Excel 2003. (I am using 2010 and 2013.)

Originally Posted by G e r a r d

The code works for me.
Seeing the same as 6StrinJazzer.
Gerard

The only difference I can see is that both you and 6StringJazzer are using Office 2010/2013 while mine is 2003. I wonder if someone can test it out with 2003.

OK that explains some...

Please Login or Register  to view this content.

Thanks so much for the quick reply...this version of the function almost satisfies all of my needs, however, it is throwing the two cells below as FALSE when they should be TRUE as they are matching. So it seems that any cells which are identical but have mixed formatting (some words or text italicized and some that are normal), are resulting in an error which is giving the FALSE output.

Column A	Column B	Match?
Warner Brothers Studios	Warner Brothers Studios	TRUE

Really appreciate all of your help Gerard!

OK, i think I found a sollution:

Please Login or Register  to view this content.

This is great, it seems to be working perfectly. Thank you so much for all your time and help!