Search for texts in adjacent cells and highlight

[AndreG06]

Hello all,

I'm quite new to VBA searching for guidance.
I was wondering if anyone had an idea how to write an excel VBA script that would do the following:
1. Search column B for a cell containing "Checker Plate".
2. And if the adjacent cell (Column A) contains a "6" or a "9" then highlight row.
3. Else do nothing.

Thanks for the help,

 

[Joe4]

Welcome to the Board!

This does not require VBA. You can use Conditional Formatting to do that. Just follow these steps:
1. Select all the rows you want to apply this to (your whole data area)
2. Go to Conditional Formatting and select "New Rule" and then the "Use a formula to determine which cells to format" option
3. Write the formula as it applies to the first row in your selection. If that is row 2, the formula would look like:

=AND($B2="Checker Plate",OR($A2=6,$A2=9))

4. Select the color option you want for highlighting
5. Click OK

This should do what you want.

 

[AndreG06]

Thanks for your reply.

It seems to be working, but from what I can see it only works if the cell is either "6" or "9" and nothing more.

Would there be a way to change the formula in a manner that if the cell contains a "6" or "9" it would still work (Exemple: ABC6).

Thanks!

 

[Joe4]

Try this Conditional Formatting formula instead:

=AND($B2="Checker Plate",SUBSTITUTE(SUBSTITUTE($A2,"6",""),"9","")<>$A2)

 

[AndreG06]

Thanks! This seems to work perfect.

I recorded the function and it gave me :

Sub SEARCH_6_9_CHECKER()
' SEARCH_6_9_CHECKER Macro

    Columns("A:B").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($B1=""Checker Plate"",SUBSTITUTE(SUBSTITUTE($A1,""6"",""""),""9"","""")<>$A1)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

The macro works on its own, but when I copy/paste it in my main formatting macro it does nothing. ?

 

[Joe4]

The macro works on its own, but when I copy/paste it in my main formatting macro it does nothing

Without your other macro, I cannot really comment on that. You may have something else overriding it.

Note, you can keep it as its own Macro, and then just call it from your other Macro, with a line of code like this:

Call SEARCH_6_9_CHECKER

 

[AndreG06]

I just figured it out. I was using the wrong keyboard shortcut ! I'll be sure to double check that in the future.

It all works perfect now.

Thanks for everything!

 

[Joe4]

You are welcome.