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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Starstopper31

New Member
Joined
Jan 19, 2015
Messages
8
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,141,816
Messages
5,708,751
Members
421,588
Latest member
Wawie

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
Top