Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
The macro ran on Column A, not D...how do I change it to column D?Change this:
Set myrng = Range("A1:A" & Range("A65536").End(xlUp).Row)
To this:Set myrng = Range("D1:D" & Range("D65536").End(xlUp).Row)
My preference would be for this highlighting to be "on the fly" as new rows are added, rather than having to run a macro...is this possible?This will work but be forewarned, it is not very efficient since it will process all records each time a change is made.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Variant
Dim myrng As Range
Dim clr As Long
If Not Intersect(Target, Range("D1:D65536")) Is Nothing Then
Set myrng = Range("D1:D" & Range("D65536").End(xlUp).Row)
myrng.Interior.ColorIndex = xlNone
clr = 3
For Each cel In myrng
If Application.WorksheetFunction.CountIf(myrng, cel) > 1 Then
If WorksheetFunction.CountIf(Range("A1:A" & cel.Row), cel) = 1 Then
cel.Interior.ColorIndex = clr
clr = clr + 1
Else
cel.Interior.ColorIndex = myrng.Cells(WorksheetFunction.Match(cel.Value, myrng, False), 1).Interior.ColorIndex
End If
End If
Next
End If
End Sub
Is there a way I can reference a custom color palette instead of the default colors so it's a little easier on the eyes?Yes, but that is much more complicated and therefore warrants a separate question.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Open in new window