Excel VBA ? Counting red coloured cells under headings and extracting them against date / month

Hi Experts

Hope you can help although I’m not sure if what I’m looking for is possible

I had some excellent assistance with this from the experts and submitted this report with a basic summary however what is required is a little more challenging

I believed using the Colorfunction () online VBA module would solve my issue of counting the coloured cells and it has for the transport summary however what is required is to devise a monthly breakdown per sheet.

This requires some modification to the code to be able to count the number of red cells against each heading (or Columns F to M) but also against the corresponding dates so it provides a count, per month, of the red cells, per heading, to the right of the main sheet

I’ve provided an example on the Air tab, I have manually entered the data in the summary to the right to show the desired results

Is this possible? I’ve tried consolidating some examples I discovered on line using the SUMPRODUCT function, but I haven’t found a suitable solution

Your suggestions would be very welcome
J
EE-Example.xlsm
Jase AlexanderCompliance ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Here are 2 ways to do it.

On sheet Air the count is done with a VBA function, checking the dates and colors.
The values in T1:AE1 are changed to dates, so the dates in column D can be compared to that.
Then formatted to show Jan, Feb etc.
The year is in S1, with the color to count.

On sheet Air (2) the count is with a SUMPRODUCT function.
The values to count are in columns F:M, where 1 is green, and 2 is red.
The color in the cells are done by conditional formatting, both cell and text, so the value is not visible in the cell, see the formula line.
I think typing 1 or 2 is easier than changing the color of the cell,
And then the calculation is with standard formulas.
EE-Example-Count-colour.xlsm
Sam JacobsDirector of Technology Development, IPMCommented:
See the attached with the 2 added macros.
On Sheet1 you simply add the following macro for each monthly total:
=SumMonthlyTotal($A$2, "colorColumn", "D", month)

where:
$A$2 = cell containing the color you are looking to count
"colorColumn" = the column you are searching (e.g. for Nav = "F", for Ricardo = "G", etc.)
"D" = the column containing the date field
month  = only dates falling in this month will be summed in this cell
   
MonthSum1.jpg  monthSum2.jpgCountColoredCells.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jase AlexanderCompliance ManagerAuthor Commented:
HI Both

Thank you so much for your response to this I cant tell you how much I appreciate your help

I learn so much from you guys

Regards
Jase
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.