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

[Sreeja]

HI,

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

Pls help

[lucas]

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]

[parttime_guy]

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

[Sreeja]

Thanks .. that worked

[parttime_guy]

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