Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I Need to compare Column B in Sheet 2 with Column C in Sheet 3 and highlight the cells with matching data in Column B (Sheet 2).

Have used the following formula in Conditional Formatting

which works =NOT(ISNA(VLOOKUP(Sheet3!C,Sheet2!B,1,FALSE))) with Format type being a

particular color say Yellow Interior.colorindex = 6

How to implement the same using code in VBA?

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

you can do it like this 

Sub CompareAndHighlight()

    Dim rng1 As Range, rng2 As Range, i As Long, j As Long
    For i = 1 To Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
        Set rng1 = Sheets("Sheet2").Range("B" & i)
        For j = 1 To Sheets("Sheet3").Range("C" & Rows.Count).End(xlUp).Row
            Set rng2 = Sheets("Sheet3").Range("C" & j)
            If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
                rng1.Interior.Color = RGB(255, 255, 0)
            End If
            Set rng2 = Nothing
        Next j
        Set rng1 = Nothing
    Next i

End Sub

the code checks all cells in Sheet2 column B against each cell from Sheet3 column C and if they match it highlights cells on Sheet2 in Column B in yellow

share|improve this answer
 
The Code works well. I had not put forth my question more clearly. The above code compares for example b2 in sheet 2 with c2 in sheet 3 and so on. I would like the each value in Sheet 3 to be checked against all the values in the other column. For which another loop would be required. Can you please edit the code to add the above mentioned specification and error handling for blank cells? –  Alcapone Aug 27 at 13:24
 
I have edited the code for you so it iterates over all cells in Column C on Sheet3. What do you mean with error handling for blank cells? –  mehow Aug 27 at 13:31
add comment
up vote 0 down vote

I would do it this way:

Dim c As Range

For Each c In Range("sheet2!b:b")
    If c.Value <> "" And Sheets("Sheet3").Cells(c.Row, 3).Value = c.Value Then
        c.Interior.Color = vbYellow
    End If
Next
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.