ka_inc
asked on
Excel VBA and Color Format
Have typical VBA to find duplicates and set the duplicate cell's background in different colors. (see below) Instead of specific cell changing color I would like multiple cells (cells from columns A to H) to be colored. How to code?
Thanks
Dim cel As Range
Dim myrng As Range
Dim clr As Long
Set myrng = Range("H2:H" & Range("H65536").End(xlUp). Row)
myrng.Interior.ColorIndex = xlNone
clr = 3
For Each cel In myrng
If Application.WorksheetFunct ion.CountI f(myrng, cel) > 1 Then
If WorksheetFunction.CountIf( Range("H2: H" & cel.Row), cel) = 1 Then
cel.Interior.ColorIndex = clr
clr = clr + 1
If clr = 56 Then clr = 3
Else
cel.Interior.ColorIndex = myrng.Cells(WorksheetFunct ion.Match( cel, myrng, False), 1).Interior.ColorIndex
End If
End If
Next
Thanks
Dim cel As Range
Dim myrng As Range
Dim clr As Long
Set myrng = Range("H2:H" & Range("H65536").End(xlUp).
myrng.Interior.ColorIndex = xlNone
clr = 3
For Each cel In myrng
If Application.WorksheetFunct
If WorksheetFunction.CountIf(
cel.Interior.ColorIndex = clr
clr = clr + 1
If clr = 56 Then clr = 3
Else
cel.Interior.ColorIndex = myrng.Cells(WorksheetFunct
End If
End If
Next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER