Solution to keep track of number of overdue dates

eiloken said:

you can create user function to do it:

VBA Code:

Function CountRedCells(ByVal rng As Range) As Long
    Dim cll As Range
    For Each cll In rng
        If cll.Interior.Color = RGB(255, 0, 0) Then CountRedCells = CountRedCells + 1
    Next cll
End Function

in result cell:

Excel Formula:

=CountRedCells("Enter range you want to count red cell")

Click to expand...

AVIWEGATES1991 said:

Thanks Alot for your tremedous help i tried to insert the VBA Code you gave me and it works for getting colors for normal cells but UNFORTUNATELY it doesnt work for what i had wished to solve i suspect its due to the cells are containing Conditional formats rules which format the cells RED if its Over due Any further help will highly be appreciated.

Click to expand...

eiloken said:

i can create a function to count cells format as conditional formatting, but maybe it not work properly as formula, you can try:

VBA Code:

Function CountColorCells(ByVal rng As Range, ByVal xcolor As Long) As Long
    Dim cll As Range
    Dim count As Long
    For Each cll In rng
        If cll.DisplayFormat.Interior.Color = xcolor Or cll.Interior.Color = xcolor Then
            count = count + 1
        End If
    Next cll
    CountColorCells = count
End Function

Sub RedCount()
    MsgBox CountColorCells(Selection, RGB(255, 0, 0))
End Sub

Click to expand...