How to check if cell contains TWO strings in VBA

Question

How can I check if a cell contains two separate strings in a cell? I tried:

ElseIf InStr(1, UCase(testVal), UCase("goku"), 1) And InStr(1, UCase(testVal), UCase("vegeta"), 1) Then
            Worksheets("Export Worksheet").Cells(i, 18).EntireRow.Interior.ColorIndex = "4"

However, this does not work...

If I use

ElseIf InStr(1, UCase(testVal), UCase("goku"), 1) & InStr(1, UCase(testVal), UCase("vegeta"), 1) Then
            Worksheets("Export Worksheet").Cells(i, 18).EntireRow.Interior.ColorIndex = "4"

It will highlight the row if "goku" OR "vegeta" are in the cell. However, I need BOTH values to be in the cell.

Please help!

EvDev · Accepted Answer · 2015-03-10 21:34:35Z

2

You want the result of InStr to be great than 0. Also use "And" not "&"

ElseIf InStr(1, UCase(testVal), UCase("goku"), 1) > 0 AND InStr(1,     UCase(testVal), UCase("vegeta"), 1) > 0 Then
        Worksheets("Export Worksheet").Cells(i, 18).EntireRow.Interior.ColorIndex = "4"

answered Mar 10, 2015 at 21:34

EvDev

1597 bronze badges

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

1 Comment

EvDev Over a year ago

The "&" will concatenate the values returned from InStr.

Collectives™ on Stack Overflow

How to check if cell contains TWO strings in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related