Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

SUM/COUNT/IF Cells Based on Color in Excel - Includes Conditional Formatting

0

Good afternoon,

As per request attached is a copy of the sample workbook.

Some cell's background color changes to Green based on conditional formatting and I would like to have an updated count of all Green colored Cells in each row in column Q.

Would you please review attached and advise. I would appreciate your help.

Regards

Answer
Discuss

Answers

0

It's not that simple. First, please understand that conditional formatting does not change the background colour. Instead, it changes the display colour. So, every cell has a background colour and a display colour. The former is set by the user, the latter by CF. By default they are the same.

And that is where the problems start. There is no way to count the display colour using formulas. They can be counted using VBA. However, when you count cells with green display colour there is no guarantee that all cells that appear green on the screen are included in the count because some of the green cells on the screen may appear green because that is their natural background colour. Therefore, counting colours is inherently unreliable.

Next, please look at the CF Manager. (CF > Manage Rules). It's messy. Some rules are overlapping. Some are duplicated and one doesn't have any format set. This is partially due to Excel trying to guess your intentions as you make changes to the worksheet. The other part is lack of attention. If you want to attach any importance to the cell colouring you need to pay close attention to the CF Manager - and that is an exasperating task because CF's automation constantly interferes.

The end of the long story is not to use VBA to count coloured cells, and since you must use VBA anyway, use VBA to colour the cells and count them. In that way you will be sure that count and display are identical.

Your question isn't laid out to receive such an answer. Therefore I will not offer one. To qualify your workbook should have the xlsm format and there should be a description like "Column I: if the value is <5 or >25 then colour and count", and you should provide a cell where to put the count or a formula in which to use it and a cell for that formula.

However, I recommend that you consider this step carefully. Once you bring VBA to your project its nature will change and, if you aren't willing or able to invest time in learning VBA, you may lose control.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login