HI,
I am trying to create a macro .. which compares each column and highlights the cells which have similar values...
Pls help
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]
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I think u need to try this which has been posted in Cool tools. This will solve all ur queries with duplicates.
Thanks .. that worked
Plz mark ur thread as "solved", if everything is okay.