VBA Excel - Find Text in in Red from column B


I have this excel file where in column B from Sheet1 i have cells where some words may be in red.

I would like to create a macro that will generate a list of unique words including the counts of each.

In sheet 1 i can have this:
Once i hit the commandButton, it would generate the list of words in red including the counts.
How can i do that?

Thanks for your help
LVL 11
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.

Saurabh Singh TeotiaCommented:
Quick thing..If the sentence has are or city which is not in red?? Do you want to count that or not??

Wilder1626Author Commented:
Good question.

I only want to count if the text is in red only.

I may have about 35000 rows of data and i may have a lot of red text also.
Saurabh Singh TeotiaCommented:
Okay can you have more then one word red in the same sentence or in the same cell??

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Wilder1626Author Commented:
Yes, it can also happen that i have more than one word in red in the same cell.
Saurabh Singh TeotiaCommented:
Now i have last question to this before i design a macro for you..between the two words i.e. red and black or even red and red will you always have space as shown in your example??
Wilder1626Author Commented:
another good question.

Something yes and sometime no.

In below picture, last would count as 3
red text
Words can also be written like: last,available,double

No space but only a character that separate words.
Saurabh Singh TeotiaCommented:
So i see you got a underscore their..so i can safely say the letter will break once the alphabet gets over now it doesn't matter whether you have underscore or space.. The reason im asking is that their is no way i can think a logic can be written when you have data like this..


And you just want to see count of the word red since it's font is red and you want to see count-->2

Or it's like..


Where are and is and red all in red font and you want to see the count of all of them..So that's why i'm confirming that either its space or underscore and their won't be any continuation like i talked to you in above examples...

Wilder1626Author Commented:
I just went over some weird words in cells and here are some examples:
The distance time option is not allowed for 'Canceled' Load ^1.
Distance Time is not allowed for 'Canceled' Shipment ^1.
The distance time option is not allowed for 'Canceled' Trip ^1.
Distance Time is not allowed for 'Purged' Shipment Leg ^1.
The address was changed and existing Distance Overrides referencing the old address might be invalid. Please indicate what should happen to these overrides.
Warning: Rating is not valid.
Distance/Transit Time Override ^1 created.
Distance/Transit Time Override created for ^1 ^2.
Distance/Transit Time Override with the same Distance Engine Version, Division Code, Tariff / Service combination, First / Second Locations already exists.
Distance/Transit Time override ^1 deleted.
There are no Distance/Transit Time Overrides defined.
Distance/Transit Time Override removed for ^1 ^2.
Distance/Transit Time Override Details State must be empty.
Distance/Transit Time Override Details State is mandatory.
Distance/Transit Time Override Details Country and State must be equal to First Location.
Distance/Transit Time Override Details are mandatory.
Distance/Transit Time Override Details Country and State must be equal to Second Location.
First and Second Locations are identical. Distance/Transit Time Override cannot be created.
Distance Engine cannot be set to '^1' when countries associated with the two addresses refer to the same Distance Engine ('^2').
Tariff ^1 doesn't have service ^2.
First and Second Locations are mandatory.
Business Hours for ^1 ^2 created.
'^1' must be earlier than '^2' for ^3.
'^1' must be earlier than '^2' for ^3 in '^4' section.
Business Hours don't exist for ^1 ^2.
Business Hours for ^1 ^2 updated.
At least one Business Hour Zone has to be specified when 'All Business Hour Zones' is checked.

Open in new window

I don't see any underscore.

Do you still see that it will not be possible or very hard?
Saurabh Singh TeotiaCommented:
I looked into this and their is no way that you can read the font colors if you have multiple font colors..Like the way you are trying to do right now..

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
Wilder1626Author Commented:
Thanks for your help
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.