PDA

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



Sreeja
03-30-2007, 06:45 AM
HI,

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

Pls help

lucas
03-30-2007, 09:48 AM
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.

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

parttime_guy
03-30-2007, 06:59 PM
I think u need to try this (http://members.iinet.net.au/~brettdj/)which has been posted in Cool tools (http://www.vbaexpress.com/cooltools.php). This will solve all ur queries with duplicates.

:hi:

Sreeja
04-02-2007, 04:01 AM
Thanks .. that worked :thumb

parttime_guy
04-03-2007, 06:41 PM
Plz mark ur thread as "solved", if everything is okay.