Hope someone can help me on this. I have one excel sheet with the color cells counting for the vacation days. But when I change the color of the cells the count does not update automatically and I have to go to each cell to press enter or double click to make the counts updated. How can I resolve this issue?
Here is one example of my formula: =cntcolor(D4:AH4,$O$1)
Thanks in advance for your help.
Hi
is this a UDF?
That is a perfect example of why you should not base calculations on cell formats.
Try to write a formula using built-in functions based on WHY the cells are formatted the way they are.
Do the cells have conditional formatting applied? If so, what is the CF rule that applies the formatting?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
This is not a UDF.
cntcolor is not a native Excel function.Originally Posted by tracyxiao
It may be part of an add-in.
I think you are right. I do not own this file but got it from someone else and try to work on the file but find this problem. How can I find whether it is a UDF or part of an add-in?
If it is in a VBA module as a FUNCTION, then it is a UDF.
ALT F11 to open VBA Editor and then look in the modules for something like
FUNCTION cntcolor(x as range....)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Function CntColor(rng As Range, Fclr As Range) As Long
For Each c In rng
If c.Interior.ColorIndex = Fclr.Interior.ColorIndex Then
CntColor = CntColor + 1
End If
Next
End Function
That is the code for CNTColor.
Changing a cell format does not trigger a calculation.
You can make the function volatile (it will recalculate when some event triggers a calculation) but you will still have the problem you're having.
This is why I advise against basing calculations on cell formats.
So there is no way to actually make it automated if we keep this UDF, right?
Not that I'm aware of.
However, someone that knows more about VBA than I do might have a solution.
Thanks. That is very helpful.
If the cells are formatted using conditional formatting then it should be relatively easy to come up with a formula to get the count based on the CF rule(s).
The cells are not formatted but we manually change the color for each cell for the holiday or vacation days.
Ok, how about using a helper column to identify whether the date(?) is a holiday or a vacation day?
Enter a H for holiday or V for vacation.
Then you just need to count the Hs or Vs.
That is a good idea. But need to add 20 columns. And the date of the calendar is with formula. I think that might be hard to do that. Another thing is the calendar includes several years. I just feel that is not the best way. Still thanks for your advice.
No good options for this.
Using a bunch of helper cells is not especially desirable.
You could still use your CNTCOLOR function and every time you apply the cell format press function key F9 to force a calculation then the formula will update.
Again, this is not ideal but those are your options.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks