Excel-VBA macro to format inconsistent text strings

[Arturo333]

It seems to be a huge challenge for Excel VBA to format inconsistent text strings and so I am looking for a solution to format the following table:

The table may contain hundreads of rows that have to be manually checked and so it is important to make it as readable as possible. Unfortunately, the column C contains a very long and messy text string that is inconsistent and cannot be changed since it is being imported from another system.


Therefore I would like to do the following:

• make all the unimportant parts of the text string in column C light-grey highlight rows (cells in columns A:C)
• if 'the_good_indicator' is found in column C highlight specific cells with certain string elements in them (e.g. phone number '00111222333')

The list of good indicator keywords might be quite long, but in this example it is just "the_good_indicator" and "00111222333" for background color and ";Names=", "PhoneNumber=", ";mobilePhoneNumber=", ";something_unimportant=YES". Denominators are <code>;</code> and <code>=</code>

The table should look like this:

I have done some research and found just partial answers, so...

The best I can get is table like this:

...which I get by using the code:

<code>Columns("C").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="the_good_indicator", _
    TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .Color = -16752384
    .TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ColorIndex = 43
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="00111222333", _
    TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .Color = -16752384
    .TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13561798
    .TintAndShade = 0
End With</code>

I also got an expert suggestion to use INSTR or Regular Expressions together with .Characters property of the Range object to do the formatting. Unfortunately, I am lacking experience and knowledge to apply these methods at the moment...
I will appreciate any code improvement suggestions that could help reaching the formatting-goal at least partly.

 

[Norie]

How do you define the 'unimportant' things in the strings?

 

[Arturo333]

'Unimportant' is anything that should be out of the reader's focus. In this case: ";Names=", "PhoneNumber=", ";mobilePhoneNumber=", ";something_unimportant=YES"