I want to count the number of colored cells in a column but I keep getting zero even when there are colored cell present
Thanks
Sub iCountColors()
Dim ws As Worksheet
Dim Rng As Range
Dim LR As Long
Set ws = ThisWorkbook.Sheets("Nodes")
With ws
LR = .Range("A" & .Rows.Count).End(xlUp).Row
Set Rng = .Range(.Cells(2, 2), .Cells(LR, 2))
End With
MsgBox "There are: " & CountColors(Rng) /2 & " Dupes in Column B"
End Sub
Function CountColors(RangeToCount As Range) As Long
Dim ColorCell As Range
Dim myCount As Long
Application.Volatile
For Each ColorCell In RangeToCount
'If the cell has any color, add it to the running count.
If ColorCell.Interior.ColorIndex = RGB(255, 0, 255) Then myCount = myCount + 1
Next ColorCell
CountColors = myCount
End Function
Bookmarks