VB Using Excel Condition Formating on Text

Question

Using VB 2010, I am trying to convert an excel file into VB and am struggling with this conditional formatting line where TextOperator:=xlContains)

I have got most of others done i.e. Excel.XlFormatConditionType.

Dim r As Excel.Range 
R = oSheet.Range("A" & CStr(FirstEmptyInColA) & ":A" & CStr(lastRow)).SpecialCells(Excel.XlCellType.xlCellTypeVisible)

          For A = 0 To UBound(AgentNames)

                BColour = BColour + 1
                If BColour > 10 Then BColour = 3
                With r
                    .FormatConditions.Add(Type:=Excel.XlFormatConditionType.xlTextString, String:=AgentNames(A), TextOperator:=xlContains)
                    .FormatConditions(r.FormatConditions.Count).SetFirstPriority()
                    With .FormatConditions(1)
                        .Interior.PatternColorIndex = oWorkbook.xlAutomatic
                        .Interior.ColorIndex = BColour
                        If BColour = 5 Or BColour = 7 Or BColour = 9 Then
                            .Font.ColorIndex = 2
                        End If
                    End With
                    .FormatConditions(1).StopIfTrue = False
                End With
            Next A
            r = Nothing

So you want just equivalent of TextOperator:=xlContains in vb.net? It means string.Contains(). Can you explain more what you need exactly? — S.Serpooshan
– S.Serpooshan, Commented Jan 16, 2017 at 17:23
I guess you're after TextOperator:=Excel.XlContainsOperator.xlContains — Bugs
– Bugs, Commented Jan 16, 2017 at 17:32
thanks guys that's exactly what I needed, I have seen plenty of examples around but they all seem to use the same syntax as Excel, yet I have to use this the full syntax can anyone please explain — JPS
– JPS, Commented Jan 17, 2017 at 7:50

Bugs · Accepted Answer · 2017-01-16 17:39:04Z

1

Looking at the FormatCondition.TextOperator documentation:

Returns or sets one of the constants of the XlContainsOperator enumeration, specifying the text search performed by the conditional formatting rule.

This being the case your code will be:

TextOperator:=Excel.XlContainsOperator.xlContains

As a note, XlContainsOperator has the following operators you can use:

answered Jan 16, 2017 at 17:39

Bugs

4,4799 gold badges34 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

W. Hans · Accepted Answer · 2017-01-16 17:33:56Z

1

Should be Excel.XlContainsOperator.xlContains

answered Jan 16, 2017 at 17:33

W. Hans

111 bronze badge

Collectives™ on Stack Overflow

VB Using Excel Condition Formating on Text

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related