Comparing each cell in a range to a different range and highlighting matches

Question

I would like the following VBA code:

I have two columns full of values. I would like to take of of those columns and, starting from the first value, check if the value is anywhere in the other column. If the value is found, I would like to highlight the entire row. then restart with the second value in the first range and so on. The two columns are on different sheets.

For example here are the columns

Worksheet1           
A1: 1234
A2: 2345
A3: 3456
etc...

Worksheet2
A1: 1111
A2: 2345
A3: 3333
etc...

I would like the vba code to check if "1234" is in worksheet2 column A and highlight the entire row in yellow if it is. Then do the same for "2345".

This site is not a coding service. We can help you with your code, but you need to post what you have so far. — teylyn, Sep 03 '14 at 21:26
@TiMeX24: totally agree with above commenter. you are expected to do a research on your own on google how to do conditional formating across worksheets in excel. we wont ;) — Krish, Sep 03 '14 at 21:52

score 0 · Answer 1 · answered Sep 03 '14 at 23:03

Hello TimeX24 I am only going to help you out but the rest you can do at the moment the code I am about to Give you is for a button what is does is takes sheet1 cell A1 and than searches for it on sheet2 in column A if found message box will pop up You may remove that code and add in you're own I just didn't want to do all the work but this will get you started.

Sub Button1_Click()
Dim ToBeFound As String
Dim rng As Range

Sheets("Sheet1").Activate
Range("A1").Activate

For x = 1 To 1000

ToBeFound = ActiveCell.Value

If ActiveCell.Value = "" Then
Exit Sub
Else
Sheets("Sheet2").Activate
Range("A1").Activate
Set rng = Cells.Find(What:=ToBeFound, LookIn:=xlValues, lookat:=xlPart)

If IIf(rng Is Nothing, "", rng) = "" Then
Sheets("Sheet1").Activate
ActiveCell.Offset(1, 0).Activate
Else
'This is where you can add some of your
'Own code to get what you want.
MsgBox ("Found " & ToBeFound)
Sheets("Sheet1").Activate
ActiveCell.Offset(1, 0).Activate
End If
 End If

Next x

End Sub

score 0 · Answer 2 · edited May 23 '17 at 12:12

Try this:

Sub marine()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("Sheet1"): Set sh2 = Sheets("Sheet2")

    Dim c As Range, sh1rng As Range, rngTofill As Range
    With sh1
        Set sh1rng = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
    End With

    Dim arr As Variant
    With Application
        arr = .Transpose(sh2.Range("A1", sh2.Range("A" & _
            sh2.Rows.Count).End(xlUp)))
        For Each c In sh1rng
            If Not IsError(.Match(c,arr,0)) Then
                If rngTofill Is Nothing Then
                    Set rngTofill = c
                Else
                    Set rngTofill = Union(rngTofill, c)
                End If
            End If
        Next
    End With
    rngTofill.EntireRow.Interior.Color = RGB(255, 255, 0)
End Sub

To improve coding and to understand most parts of the code read this.
If you still have questions, comment it out. HTH.

Btw, there's always a first time post. But next time make sure to
provide relevant information in your question specially the things you've tried.

Comparing each cell in a range to a different range and highlighting matches

2 Answers2