Search for texts in adjacent cells and highlight

AndreG06

New Member
Joined
Sep 18, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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,
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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:
Excel Formula:
=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.
 
Upvote 0
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!
 
Upvote 0
Try this Conditional Formatting formula instead:
Excel Formula:
=AND($B2="Checker Plate",SUBSTITUTE(SUBSTITUTE($A2,"6",""),"9","")<>$A2)
 
Upvote 0
Thanks! This seems to work perfect.

I recorded the function and it gave me :

VBA Code:
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. ?
 
Upvote 0
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:
VBA Code:
Call SEARCH_6_9_CHECKER
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top