Solution to keep track of number of overdue dates

AVIWEGATES1991

New Member
Joined
Apr 13, 2024
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Good day everyone need assistance if there is one to write me a code in excel that can count or solution to counting number of cells RED in given row. in this case i want to create a column that can keep track of Overdue whichis represented as RED dates in real time the lines represent the current date.
Thanks
 

Attachments

  • Screenshot 2024-04-24 042936.png
    Screenshot 2024-04-24 042936.png
    103 KB · Views: 29

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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")
 
Upvote 0
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")
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.
 
Upvote 0
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.
Cell Formulas
RangeFormula
M6M6=IF(display="Quarterly",DATE(YEAR(project_start),1+3*(display_period-1),1),IF(display="Monthly",DATE(YEAR(project_start),MONTH(project_start)+(display_period-1),1),$C$4-WEEKDAY(project_start,1)+2+7*(display_period-1)))
N6:BL6N6=IF(Show_weekends,M6+1,WORKDAY.INTL(M6,1,Weekend_option))
M7:BL7M7=IF(display="Quarterly","Q"&CHOOSE(MONTH(M6),1,1,1,2,2,2,3,3,3,4,4,4),IF(display="Monthly",LEFT(TEXT(M6,"mmm"),1),IF(display="Weekly",WEEKNUM(M6,21),LEFT(TEXT(M6,"ddd"),1))))
I8:I18I8=IF(OR(F8=0,E8=0)," - ",NETWORKDAYS(E8,F8))
J8:J18J8=COUNTIFS($M8:$CY8,AND($H8<1,TODAY()>$M8))
K8:K18K8=ROUNDDOWN(H8*G8,0)
L8:L18L8=IF((G8-K8)=0,"-",G8-K8)
F8:F18F8=IF(ISBLANK(E8)," - ",IF(G8=0,E8,E8+G8-1))
Named Ranges
NameRefers ToCells
display='Project Planner'!$H$5M6, M7:BL7
display_period='Project Planner'!$H$4M6
Show_weekends=Settings!$C$14N6:BL6
Weekend_option=Settings!$C$3N6:BL6
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M6:CY18,M21:CY25Expression=AND(TODAY()>=M$6,TODAY()<N$6)textNO
H8:H18,H21:H25Other TypeDataBarNO
M6:CY7Expression=M$6=TODAY()textNO
M8:CY18,M21:CY25Expression=AND($E8<=M$6,ROUNDDOWN(($F8-$E8+1)*$H8,0)+$E8-1>=M$6)textNO
M8:CY18,M21:CY25Expression=NOT(AND(NOT(ISBLANK($E8)),$E8<M$6,$F8>=M$6))textYES
M8:CY18,M21:CY25Expression=AND(H$9<1,TODAY()>M$6)textNO
M8:CY18,M21:CY25Expression=TRUEtextNO
M6:CY18Expression=NETWORKDAYS(M$6,M$6)=0textNO
 
Upvote 0
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
 
Upvote 1
Solution
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
Thanks Alot it solved my Query after Playing around your CODE
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top