Solution to keep track of number of overdue dates

A

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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
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
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...
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
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...
GM Project working.xlsx
EFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBL
6 18 March 202419 March 202420 March 202421 March 202422 March 202423 March 202424 March 202425 March 202426 March 202427 March 202428 March 202429 March 202430 March 202431 March 202401 April 202402 April 202403 April 202404 April 202405 April 202406 April 202407 April 202408 April 202409 April 202410 April 202411 April 202412 April 202413 April 202414 April 202415 April 202416 April 202417 April 202418 April 202419 April 202420 April 202421 April 202422 April 202423 April 202424 April 202425 April 202426 April 202427 April 202428 April 202429 April 202430 April 202401 May 202402 May 202403 May 202404 May 202405 May 202406 May 202407 May 202408 May 2024
7STARTENDDAYS% DONEWORK DAYSOVER DUEDAYS DONEDAYS LEFT12121212121212131313131313131414141414141415151515151515161616161616161717171717171718181818181818191919
8 - - 00-
9Mon 3/25/24Thu 5/02/243970%2902712
10Wed 3/27/24Thu 4/11/2416100%12016-
11Mon 4/01/24Sun 4/14/241470%10095
12Wed 4/10/24Sat 4/13/24470%3022
13Sun 4/14/24Mon 4/29/241650%11088
14Mon 4/22/24Wed 4/24/24350%3012
15Thu 4/25/24Sat 5/04/241067%7064
16 - - 00-
17Fri 4/19/24Thu 5/02/241450%10077
18Thu 4/25/24Tue 5/14/242060%140128
Project Planner
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
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...
Thanks Alot it solved my Query after Playing around your CODE
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
How to create monthly report for activities based on created date, closed date, and due date, for past year?
Replies
1
Views
469
josevaldesv1
J
E
  • Question Question
Stop counting cells when status is complete
Replies
4
Views
503
estanden
E
S
  • Question Question
How can I exactly count NETWORKDAYS in multiple date ranges, after a specific date?
Replies
1
Views
282
Tetra201
Tetra201
S
  • Question Question
Look up todays date and special copy paste value for multiple sheets
Replies
4
Views
228
OaklandJim
O
K
  • Solved
help with formatting a cell (for TIME, custom format) that retrieves its value from another cell...
Replies
2
Views
371
kbishop94
K

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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