Conditional formatting _ array of string criteria

pulsar777 · Dec 12th 2016

Hi all,

I'd like to apply 1 formatting rule,
with multiple text conditions.

Something like this:

Code

With Range("A:A")
    .FormatConditions.Add Type:=xlTextString, String:=Array("01234", "56789"), TextOperator:=xlContains
    .FormatConditions(1).Interior.Color = vbYellow
End With

I'd like to avoid:
a) looping Arrays and Cells on worksheet to apply formatting individually based on If statement (too much looping ..)
b) creating =OR() function in conditional formatting setup, while array would probably exceed that functions arguments limit.

Thank you for any advice!

**Ger Plante** · Dec 12th 2016

Re: Conditional formatting _ array of string criteria

Wont work because "xltextstring" it is expecting a string.... you are trying to give it an array.

You could do this...

Code

Sub Macro1()
    Dim x As Variant
    Dim iloop As Integer


    x = Array("01234", "56789")
    
    With Range("A:A")
        .FormatConditions.Delete
        For iloop = LBound(x) To UBound(x)
            .FormatConditions.Add Type:=xlTextString, String:=x(iloop), TextOperator:=xlContains
            .FormatConditions(.FormatConditions.Count).Interior.Color = vbYellow
        Next
    End With
    
End Sub

Display More

Same difference and it also gives you the ability to specify different colours with a select case statement.

By the way, dont specify "A:A" as the range... that would slow things down considerably.

Ger

pulsar777 · Dec 12th 2016

Re: Conditional formatting _ array of string criteria

Hi Ger Plante,

thanks. Though, Array often changes and might have even 1000 strings.
Not sure how many separate rules might I make by your code.

Probably to copy Array to some range and change type from
Type:=xlTextString

to

Type:=xlExpression

and apply by a MATCH function?

Or might be there a better way?

**Ger Plante** · Dec 12th 2016

Re: Conditional formatting _ array of string criteria

Quote

even 1000 strings

I tested it 1000 strings and it worked...

You obviously need a mechanism to know or figure out what those 1000 strings are... and when you do, you should call the macro with that array of strings like below, so you can call it any time you want.

Code

Sub Macro1(x as variant) 


    Dim iloop As Integer 
    With Range("A:A") 
        .FormatConditions.Delete 
        For iloop = LBound(x) To UBound(x) 
            .FormatConditions.Add Type:=xlTextString, String:=x(iloop), TextOperator:=xlContains 
            .FormatConditions(.FormatConditions.Count).Interior.Color = vbYellow 
        Next 
    End With 
     
End Sub 


Sub test() 
    Dim x As Variant 
    x = Array("01234", "56789") 
    macro1 (x)
end sub

Display More

Using the match function (or countif) would also work... yes.

pulsar777 · Dec 13th 2016

Re: Conditional formatting _ array of string criteria

Great, thanks!

Conditional formatting _ array of string criteria

Participate now!

Share