Conditional formatting using multiple conditions in Macro/VBA

[jbuist]

Hello!

I am trying to create a Macro/VBA to conditionally format text. I have 30+ text examples I'd like to highlight all the same color. For example, I'd like to highlight: "abc called", "abc understands", "abcdef called", etc. I don't know how to do this other than recording each and every 'text contains' in conditional formatting. I've got to assume Excel/VBA has this capability to perform in a Macro, I just don't know what to do. I can record Macros, but don't know how to write or modify them well. Any help is appreciated!

Example:

Columns("J:J").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="abc called", _
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
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:= _
"abc understands", 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
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="abcdef called" _
, 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


So the above does what I need, but this will get rather lengthy. Can I combine multiple text to highlight in the same function?

Thank you!!!

 

[Joe4]

Welcome to the Board!

I don't think you can do 30 levels of Conditional Formatting without an add-in like this one here: http://www.xldynamic.com/source/xld.CFPlus.Download.html

Or, I would recommend setting up a Worksheet_Change event procedure which runs when a cell is manually updated. Then what I would probably do is to store the 30 values in an array, and loop through that array, and apply the format if the condition is met. Likewise, you could store the 30 values on a sheet in your file somewhere, and loop through that instead of an array. The advantage to that method is if this list ever needs to be updated, whoever is doing that does not need to mess with the VBA code.

 

[jbuist]

Thank you Joe!

I'm not sure the array would work as this data isn't manually updated in the workbook (it's an extract from a system). I appreciate the add on suggestion. Anything else you can suggest?

Thanks again! Really appreciate your help.

J.

 

[Joe4]

I'm not sure the array would work as this data isn't manually updated in the workbook (it's an extract from a system).

Are you talking about the words/phrases you are searching for to highlight, or the data you are searching in?
I am talking about storing the word/phrases you are searching for to highlight in the array.

I was assuming you were too, otherwise you would be having to set up your Conditional Formatting, Macros, or whatever every time if the words you were looking for changed every time.

If that is correct, my array proposal suggestion should still be in play.

 

[jbuist]

Got it. Yes, you are correct. Thanks again!

 

[Joe4]

You are welcome.
If you have any questions or issues applying that methodology, let me know.

 

[Peter_SSs]

Welcome to the MrExcel board!

If you set up the list in a worksheet as suggested, then perhaps you can do what you want without a macro. It would be something like this.

The list of items to highlight:

Excel Workbook
	A
1
2	abc called
3	abc understands
4	abcdef called
5
List

Then in the actual sheet, set up CF like this

Excel Workbook
	J
1	Data
2	abc called
3	Other 3
4	Other 4
5	abc called
6
7	abc understands
8	abcdef called
9	Other 8
10	Other 9
11	Other 10
12	abc called
13	Other 12
14	abcdef called
15	Other 14
16	abc understands
CF Multiple (manual)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2	1. / Formula is =MATCH(J2,List!A$2:A$40,0)	Abc

However, if you need a macro and don't want to have the items to highlight in a worksheet, you could try a macro like this.

Sub SetUpCF()
  Const myVals As String = "|abc called|abc understands|abcdef called|" _
                            & "Text 4|Text 5|Text 6|Text 7|Text 8|Text 9|Text 10|" _
                            & "Text 11|Text 12|Text 13|Text 14|Text 15|Text 16|Text 17|"
                            
  'Add all your items to the list above
  
  Columns("J").FormatConditions.Delete
  With Range("J2", Range("J" & Rows.Count).End(xlUp))
    .FormatConditions.Add Type:=xlExpression, Formula1:="=SEARCH(""|""&J2&""|"",""" & myVals & """)"
    .FormatConditions(1).Interior.Color = vbYellow
  End With
End Sub

The result for me:

Excel Workbook
	J
1	Data
2	abc called
3	Other 3
4	Other 4
5	abc called
6
7	abc understands
8	abcdef called
9	Other 8
10	Text 15
11	Other 10
12	abc called
13	Other 12
14	abcdef called
15	Other 14
16	abc understands
CF Multiple (vba)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2	1. / Formula is =SEARCH("|"&J2&"|","|abc called|abc understands|abcdef called|Text 4|Text 5|Text 6|Text 7|Text 8|Text 9|Text 10|Text 11|Text 12|Text 13|Text 14|Text 15|Text 16|Text 17|")	Abc

 

[jbuist]

I was able to test this and it works great if the text is the only text in the cell. However, the phrases are only part of the text string. Meaning, I'd like to highlight the cell if it contains the phrase "abc called", "abc understands", etc. I'm just a beginner when it comes to coding and I truly appreciate your help. Thanks!!

 

[Peter_SSs]

I was able to test this and it works great if the text is the only text in the cell. However, the phrases are only part of the text string. Meaning, I'd like to highlight the cell if it contains the phrase "abc called", "abc understands", etc. I'm just a beginner when it comes to coding and I truly appreciate your help. Thanks!!

With the list in sheet 'List' as per my previous example

Excel Workbook
	J
1	Data
2	abc called on Monday
3	Other 3
4	Other 4
5	When abc called it was hot
6
7	abc understands very well
8	abcdef called
9	Other 8
10	Other 9
11	Other 10
12	abc called
13	Other 12
14	Yesterday abcdef called
15	Other 14
16	abc understands
CF Multiple

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2	1. / Formula is =LOOKUP(9.99E+307,SEARCH(" "&List!$A$2:$A$4&" "," "&J2&" "))	Abc

Or apply that CF (still using 'List' sheet) by vba

Sub SetUpCF_v2()
  Columns("J").FormatConditions.Delete
  With Range("J2", Range("J" & Rows.Count).End(xlUp))
    .FormatConditions.Add Type:=xlExpression, Formula1:="=LOOKUP(9.99E+307,SEARCH("" ""&List!$A$2:$A$4&"" "","" ""&J2&"" ""))"
    .FormatConditions(1).Interior.Color = vbYellow
  End With
End Sub