Need CountCellsByColor formula to update automatically without pressing "F2" "Enter"

Hi,
I currently have a module that allows the "Mil Pay" tab of my workbook to run a CountCellsByColor formula. The problem i am running into is if i change the color of the cell I am referencing the formula will not update unless I press "F2" and "Enter". My goal is really to have a numerical value of 1 populate in cells "D,F,H,J" when the color in cells "E,G,I,K" change to green or RGB 0,255,0. If there is a better way for this to happen (and have it update when the cell is changed from green to no fill automatically) I would be very appreciative if you could show me.


Attached below is the spreadsheet with my current progress on the 'Mil Pay" tab and testing out the formula in cell D2.

I forgot to add the module that is enabling the CountCellsByColor formula is "Module1" please disregard the other module

Conditional formatting could probably accomplish it, if you explain your criteria for coloring the cells?

My suggestion is to upload a Before and After demonstrating what you actually want. It is a lot easier than trying to reverse engineer what you have already accomplished. Basically remove the superfluous information, and dumb down the workbook, so we can clearly see what you are trying to accomplish, and how. Manually mock up your results, and make sure there are enough examples to see how you expect your results to turn out.

TheN I did not think of a before and after but that probably would have been a good way to go. I unfortunately do not have a before any longer.

My criteria for coloring the cells is to double click a cell (E2 in this case which shows the date the report is due) once a report is completed to turn it green. I would like a numerical value returned in another cell (D2) once the cell (E2) is colored green. The numerical value will help with other formulas I would like to do on this workbook as it is easier to deal with numerical values than colors. I hope this helps and thank you for your input!

TheN thanks for the reply but I am trying to avoid having more columns and having to type in any text on this workbook. There are also additional columns I would like to add later. If there is no solution for my problem I will go the route you suggested with check marks. If possible though I would like to make it easy for someone to simply double click a cell and have it turn green and then get a numerical value in column D which will be hidden later if it is able to be done.

Please see my edited answer, I believe the second part is pretty much what you are asking for.

This time, I used:

Please Login or Register  to view this content.

for the conditional formatting, and based it on your double click producing a 1 in the cell to the left of the cell you are clicking on.

It can obviously be tweaked further, but that should demonstrate the basic idea? That is what you were asking for, more or less correct? Or I am I missing some other expected result?

TheN What I am trying to accomplish is to have the 1 generate from the green background color in cell E. Column D will be hidden if I am able to accomplish this as I am trying to avoid having to type anything into the spreadsheet if possible. The main goal is to have column D return the value of 1 if column E is green. The reason for this is my original spreadsheet allows someone do double click a cell and turn it green. In the end you will only see columns E,G,I,K as columns D,F,H,J will be hidden.

I am saying that you can tweak the end result of the double click to produce a numeric value in the cell, and base everything else off of that. Or, you can simply add in a string that does that if your code works already. Dealing with colors is quite frankly, a pain in the butt, which is why I was suggesting you just use conditional formatting and eliminate dealing with colors in VBA. Also, you have the double clicking applied to the whole sheet instead of individual ranges.

How about you watch this and tweak it ever so slightly to work for your workbook (basically flip around the rows in either the workbook itself, or your VBA).

https://www.youtube.com/watch?v=-B2R52y4eK4

He uses Data validation and things like that which will make the workbook a lot more robust. You also don't have it set up so the entire sheet turns green when double clicked.

Here's the original code for that video if you don't feel like watching the whole thing:

Please Login or Register  to view this content.

TheN I appreciate the post I have it set up so a single cell turns green IE when you click on a date in cell D to turn it green and I am ok with it being applied to the whole sheet as other sheets have different ranges and those ranges can change in the future. I would like to keep the date in the cell if I can instead of putting a numeric number in that particular cell. I know dealing with colors is a pain as I have tried working with this for weeks but that is why I am looking for help at this point. I will look at the code you provided above and see if I can tweak my current code and see if that works but if anyone else has any other ideas or how to make it so my current formula doesn't need me to press F2 and Enter that would be very much appreciated as well.

Thank you again TheN for your suggestions!