Count Cells in Date Range and Cell Color (using VBA)

Hi all - First time posting here. Thanks in advance for the help. I'm familiar with basic Excel functions and formulas. Running Office 365. As part of a recent project, I have a list of dates. I need to count the number of cells within a date range AND if they colored red or green. The team uses red/green to flag a specific aspect to minimize data tracking.

I can easily count the cells in the date range using the COUNTIFS function (e.g. =COUNTIFS(B14:B17,">=8/1/2021",B14:B17,"<=8/31/2021"). Using a macro found online, I am able to successfully count the number of cells of each color by creating a custom function called "colorfunction" (e.g. =colorfunction(F2,B14:B17,FALSE, where F2 is the reference cell color, the TRUE/FALSE statement is for summing and has no relevance to my use).

Unfortunately, I'm unable to nest the "colorfunction" function within the COUNTIFS function as a third criteria to accomplish my final goal (e.g. =COUNTIFS(J1:J5,">=8/1/2021",J1:J5,"=8/31/2021",J1:J5,"=colorfunction(F2,J1:J5,FALSE)"). I tried variations within the COUNTIFS equation, but this is where my lack of VBA experience shines.

Any support is greatly appreciated!

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

Try this VBA solution

Option Explicit

Sub CntGrn()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim Mth As Integer
    Mth = InputBox("What month to find? Use Month Number, ie. 8 for August")
    Dim x As Long
    x = 0
    For i = 9 To lr
        If Month(Range("A" & i)) = Mth And Range("A" & i).Interior.ColorIndex = 43 Then
            x = x + 1
        End If
    Next i
    Range("D14") = x
End Sub

It is instead of your code. It does not use the countifs function.

It loops through column A. It asks that if it falls within the month asked in the input box and the color of the cell is green then it accumulates that count and places it in cell you have indicated for the result.

How to install your new code

• Copy the Excel VBA code
• Select the workbook in which you want to store the Excel VBA code
• Press Alt+F11 to open the Visual Basic Editor
• Choose Insert > Module
• Edit > Paste the macro into the module that appeared
• Close the VBEditor
• Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

• Press Alt-F8 to open the macro list
• Select a macro in the list
• Click the Run button