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.
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