We help IT Professionals succeed at work.

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

66 Views
Last Modified: 2019-02-07
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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
Citrix Technology Professional / Director of TechDev Services, IPM
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Jase AlexanderCompliance Manager

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions