Solved: Use VBA to compare two columns in Excel and highlight the cells that are similar

HI,

I am trying to create a macro .. which compares each column and highlights the cells which have similar values...

Pls help

Here's one way...you have to select each range when input box pops up. You might search the forum for Duplicate master which is an addin created by a vbaExpress member brettdj. Might be in cool tools.

[vba]Sub Compare_Ranges()
'On Error Resume Next
Dim rng1 As Range
Dim rng2 As Range
Dim rCell As Range
Dim result As Byte

Set rng1 = Application.InputBox(Prompt:="Enter range which you want to compare.", Title:="Criteria Range", Type:=8)
Set rng2 = Application.InputBox(Prompt:="Enter data range.", Title:="Data Range", Type:=8)
For Each rCell In rng1
rCell.Interior.ColorIndex = xlNone
rCell.Validation.Delete
result = WorksheetFunction.CountIf(rng2, rCell)
If result = 0 Then
rCell.Interior.ColorIndex = xlNone
ElseIf result = 1 Then
rCell.Interior.Color = vbGreen
With rCell.Validation
.Add xlValidateInputOnly
.InputMessage = "The value is " & result & "time occured in " & rng2.Address & "."
End With

ElseIf result = 2 Then
rCell.Interior.Color = vbYellow
With rCell.Validation
.Add xlValidateInputOnly
.InputMessage = "The value is " & result & " time occured."
End With

ElseIf result = 3 Then
rCell.Interior.Color = vbBlue
With rCell.Validation
.Add xlValidateInputOnly
.InputMessage = "The value is " & result & " time occured."
End With
ElseIf result = 4 Then
rCell.Interior.Color = vblavender
With rCell.Validation
.Add xlValidateInputOnly
.InputMessage = "The value is " & result & " time occured."
End With

End If
Next
End Sub[/vba]

I think u need to try this which has been posted in Cool tools. This will solve all ur queries with duplicates.

:hi:

Thanks .. that worked :thumb

Plz mark ur thread as "solved", if everything is okay.