Format Painter in conjunction with ColorFunction to force recalc of formulas

Good morning. I'm using the ColorFunction to find and sum specific cells in my worksheets. I have a table in a hidden worksheet where the sum of the specific cells is calculated and stored for future use when the user calls up a userform to show totals.

The formula works great right up to the point that I copy and paste a new range (which is located in the same hidden worksheet referenced above). Once the new range the cells that have the ColorFunction formulas in them become blank and the only way for them to re-calculate is to double click into the cell and hit enter. This forces the recalculation just fine but I need the formula to automatically update....how can I get this to occur?

My setting are set to Automatically recalculate. My cells are formatted for numbers. I've tried hitting F9 after adding the new range but that does not force a recalculation.

I read online something about using Format Painter when copy and pasting cells or range of cells so that the ColorFunction will automatically update; apparently a change in color does not cause a "change" enough for Excel to recognize the need to recalculate (or something like that...I think).

Has anyone had any experience using this approach and if so how would I go about using it? I've attached the workbook that contains the formulas I've reference above (see Sheets "Hidden"). The password for all sheets is "j".

Thanks.

Matthew

Kind of hard to test anything, all the worksheets are protected.

You could try adding this to ColorFunction though.

Please Login or Register  to view this content.

Colour should reflect data, not represent data. This way madness lies...

I mean that your calculations should be based on data rather than on how cells have been coloured. From a cursory look at the workbook, it seems you could replace a formula like this for instance:
=IFERROR(ColorFunction($P$2,RCS!$C$1:$C$200,TRUE),"")
with this:
=SUMIF(RCS!$B:$B,"Total Hours",RCS!$C:$C)

Glad to help.