Hi, i have been using the Get Color Count Formula to count all red cells in a workbook (there is a macro in the background to allow this to work). However after turning a cell red I have to click into the formula and press enter for it to update. This is a particular issue as it is a workbook that I have created for other users to update who aren't very confident with Excel.
Here is the Macro I used:
Does anyone know how to fix this?
Please Login or Register to view this content.
Changing a cell colour does not trigger a recalculation. What I do in applications that use this technique is to create a button(s) to set the colour(s), and within the code attached to the button(s), I do a manual sheet calculate.
May be make it volatile? If any other cell other than cells in your arguments change.
I.E. Add Application.Volatile line to your UDF.
Note: You will need to reconfirm the formula to make it volatile (if it's in a cell already). It will reset and apply volatility.
Otherwise, you'll need some other trigger.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Please ignore this, I was trying to delete the comment but cannot find the button.
Thank you for the suggestion, however I am concerned it would confuse the staff that need to use it.Originally Posted by Bob Phillips
I have tried to pop it in the code, however it hasn't made a difference. I apologies, I'm not too clued up with VBA, and generally use code I find off of the internet so don't actually understand the logistics of it..
Unless there is some other cell value change etc is taking place in the sheet, or some calculation update is taking place else where in the sheet, Application.Volatile will not impact.
In that case you'd want to trigger re-evaluation of the UDF through some other tigger.
Exactly "how" you are going to trigger will depend on your workbook set up and what you can live with.
Ex: Say that range "A1:A10" is the argument supplied to the UDF. You could trigger the re-evaluation whenever selection change occurs on the sheet and it's not in "A1:A10" range. Though there are some drawbacks.
Other options that I can think of are...
1. If it's based on single column, make use of AutoFilter (filter by color) and Subtotal() function.
2. Use legacy Excel4Macro functions combined with Named Range(can't remember where I posted this as it was few years ago...)
3. If there is some logic/condition for coloring cell red. Then count based on condition (though I'm not sure what process you use).
Though it may be much simpler to just tie it to button that's always visible at top of the sheet.
If you need further help, I'd recommend uploading sample workbook that replicates your actual workbook with desensitized info.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks