Highlight all cells if same value exists on another column (VBA only)

Starstopper31

New Member
Joined
Jan 19, 2015
Messages
8
Hello,

I am trying to highlight all the cells within a range on one sheet ("Sheet 1") if the value appears on another sheet ("Sheet2"). I was able to make the codes below work by themselves, but each code only works on one row at a time (either for "A" or "B" on sheet 1). Is there a way to combine the codes? I feel like it should be simple to expand the range to include both the "A" and "B" columns, but every time I try, I get various errors. I also want to avoid conditional formatting and would prefer to stick with VBA. Any help would be appreciated. Thanks.

VBA Code:
Sub HighlightCellIfValueExistsinAnotherColumnA()
    Dim ws As Worksheet
    Dim x As Integer
    Dim Find As Variant
     Set ws1 = Worksheets("Sheet1")
     Set ws2 = Worksheets("Sheet2")
        For x = 1 To ws1.Range("A" & Rows.Count).End(xlUp).Row
        Set Find = ws2.Range("H:H").Find(What:=ws1.Range("A" & x).Value, LookAt:=xlWhole)
        If Not Find Is Nothing Then
            If ws2.Cells(Find.Row, 6).Value = 0 And ws2.Cells(Find.Row, 9).Value = 0 Then
                ws1.Range("A" & x).Interior.ColorIndex = 4
            End If
        End If
    Next x
End Sub

Sub HighlightCellIfValueExistsinAnotherColumnB()
    Dim ws As Worksheet
    Dim x As Integer
    Dim Find As Variant
     Set ws1 = Worksheets("Sheet1")
     Set ws2 = Worksheets("Sheet2")
        For x = 1 To ws1.Range("B" & Rows.Count).End(xlUp).Row
        Set Find = ws2.Range("H:H").Find(What:=ws1.Range("B" & x).Value, LookAt:=xlWhole)
        If Not Find Is Nothing Then
            If ws2.Cells(Find.Row, 6).Value = 0 And ws2.Cells(Find.Row, 9).Value = 0 Then
                ws1.Range("B" & x).Interior.ColorIndex = 4
            End If
        End If
    Next x
End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
How about
VBA Code:
Sub HighlightCellIfValueExistsinAnotherColumnA()
    Dim ws As Worksheet
    Dim x As Integer
    Dim Find As Variant
     Set ws1 = Worksheets("Sheet1")
     Set ws2 = Worksheets("Sheet2")
        For x = 1 To ws1.Range("A" & Rows.Count).End(xlUp).Row
        Set Find = ws2.Range("H:H").Find(What:=ws1.Range("A" & x).Value, LookAt:=xlWhole)
        If Not Find Is Nothing Then
            If ws2.Cells(Find.Row, 6).Value = 0 And ws2.Cells(Find.Row, 9).Value = 0 Then
                ws1.Range("A" & x).Interior.ColorIndex = 4
            End If
        End If
        Set Find = ws2.Range("H:H").Find(What:=ws1.Range("B" & x).Value, LookAt:=xlWhole)
        If Not Find Is Nothing Then
            If ws2.Cells(Find.Row, 6).Value = 0 And ws2.Cells(Find.Row, 9).Value = 0 Then
                ws1.Range("B" & x).Interior.ColorIndex = 4
            End If
        End If
    Next x
End Sub
 
Upvote 0
Solution
How about
VBA Code:
Sub HighlightCellIfValueExistsinAnotherColumnA()
    Dim ws As Worksheet
    Dim x As Integer
    Dim Find As Variant
     Set ws1 = Worksheets("Sheet1")
     Set ws2 = Worksheets("Sheet2")
        For x = 1 To ws1.Range("A" & Rows.Count).End(xlUp).Row
        Set Find = ws2.Range("H:H").Find(What:=ws1.Range("A" & x).Value, LookAt:=xlWhole)
        If Not Find Is Nothing Then
            If ws2.Cells(Find.Row, 6).Value = 0 And ws2.Cells(Find.Row, 9).Value = 0 Then
                ws1.Range("A" & x).Interior.ColorIndex = 4
            End If
        End If
        Set Find = ws2.Range("H:H").Find(What:=ws1.Range("B" & x).Value, LookAt:=xlWhole)
        If Not Find Is Nothing Then
            If ws2.Cells(Find.Row, 6).Value = 0 And ws2.Cells(Find.Row, 9).Value = 0 Then
                ws1.Range("B" & x).Interior.ColorIndex = 4
            End If
        End If
    Next x
End Sub
Thank you, that works a lot better than anything I was able to come up with. I was hoping there would be an easier way of expanding the column ranges because I ended up needing to fill in columns A-P (I had used A & B as an example). I basically just cut and pasted that section over and over, so I ended up with one area for A, B, C, and so on. A bit clunky, but it works very well now. Thanks again.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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