Excel VBA help - find cell content and highlight

BWA · 09-10-2007, 08:01 AM

Hi,

I want to try and create a macro that will start with the value in the first cell of one column and find it another column and highlight both itself and the other value and then move onto the next value in the cell below and repeat the same process until it reaches the end of the column.

This would result in leaving unhighlighted cells as new items as it were that did not exist before.

If anyone can help or give me a few pointers, I would be very grateful.

Cheers,

B.

**Glaswegian** · 09-10-2007, 02:02 PM

Hi

Various ways to do this. Here's some code that I use (or modify) for a similar kind of thing. Basically, it matches the first cell in the first range against each cell in the second range and so on. If there is a match, then it turns the cell in the second range green. If there is no match, then it turns the cell in the first range red. The ranges do not have to be of equal size.

Code:

Sub FindDuplicates()
Dim Rng1 As Range
Dim Rng2 As Range
Dim bMatch As Boolean
Dim origRng As Range
Dim compRng As Range

On Error Resume Next
Set origRng = Application.InputBox("Choose the first range", "Range 1", Type:=8)
    If origRng Is Nothing Then Exit Sub
Set compRng = Application.InputBox("Choose the second range", "Range 2", Type:=8)
    
    For Each Rng1 In origRng
        bMatch = False
        For Each Rng2 In compRng
            If Rng1 = Rng2 Then
                bMatch = True
                Rng2.Interior.ColorIndex = 4
            End If
        Next Rng2
            If bMatch = False Then
                Rng1.Interior.ColorIndex = 3
            End If
    Next Rng1
End Sub

Let me know if this helps.

09-10-2007, 08:01 AM	#1 (permalink)
BWA Registered User Join Date: Jun 2005 Posts: 8 OS: Windows 2000	Excel VBA help - find cell content and highlight Hi, I want to try and create a macro that will start with the value in the first cell of one column and find it another column and highlight both itself and the other value and then move onto the next value in the cell below and repeat the same process until it reaches the end of the column. This would result in leaving unhighlighted cells as new items as it were that did not exist before. If anyone can help or give me a few pointers, I would be very grateful. Cheers, B.

09-10-2007, 02:02 PM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,559 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel VBA help - find cell content and highlight Hi Various ways to do this. Here's some code that I use (or modify) for a similar kind of thing. Basically, it matches the first cell in the first range against each cell in the second range and so on. If there is a match, then it turns the cell in the second range green. If there is no match, then it turns the cell in the first range red. The ranges do not have to be of equal size. Code: Sub FindDuplicates() Dim Rng1 As Range Dim Rng2 As Range Dim bMatch As Boolean Dim origRng As Range Dim compRng As Range On Error Resume Next Set origRng = Application.InputBox("Choose the first range", "Range 1", Type:=8) If origRng Is Nothing Then Exit Sub Set compRng = Application.InputBox("Choose the second range", "Range 2", Type:=8) For Each Rng1 In origRng bMatch = False For Each Rng2 In compRng If Rng1 = Rng2 Then bMatch = True Rng2.Interior.ColorIndex = 4 End If Next Rng2 If bMatch = False Then Rng1.Interior.ColorIndex = 3 End If Next Rng1 End Sub Let me know if this helps. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner