vba not updating values

[cgrubb]

Hello. I've found the following bit of code online, to add a =countccoolor function to excel, which counts the number of cells in a range that have a certain color. The range is the "A" column, and I color whole rows based on case status.

Please Login or Register  to view this content.

This lets me enter the following in cell A61: =countccolor(A$2:A59;B61)

However, if I change the color of a cell (i.e. a whole row) in the range, the totals in the cells using the =countccolor function don't update. I have to click them and press f2 (or just double click them) and then enter, before the count changes. How can I make them update/run/execute/whatever it's called, whenever a I change a the formatting of a row?

[TMS]

Mmmm, not sure you can. Colour is not data so changing the colour won't initiate a re-calculation. When you press F2 and Enter you are, effectively, changing a cell so Excel re-calculates.

You might get away with pressing F9 to calculate

Regards, TMS

[NeedForExcel]

You can probably call the function on a Selection_Change event.. Coz Application.Volatile might not work because the sheet does not re-calculate when the color is applied..

Selection_Change might be the only way I know where you will have to click somewhere else once for the code to get triggered...

[cgrubb]

I will try that, since application.volatile didn't seem to work. Where in the code should I add the line?

[NeedForExcel]

Something like this

Please Login or Register  to view this content.

Be sure to put it on the right worksheet module.

Also change the sheet name and range as required.

[cgrubb]

Thank you for your time and suggestions but it didn't work... I've made sure to enable events, restarted excel etc. Still no auto update :\

[Jonmo1]

I would suggest using a different method to flag and count cells.
Color (or any cell format) is just not a very effective means to flag cells for counting. It's not really data.

What is the reason that a cell would be colored or not colored in the first place?
Whatever criteria you use in your head to choose whether a cell is colored or not can probably be used as a criteria in a countif(s) type of function.

[NeedForExcel]

Thank you for your time and suggestions but it didn't work... I've made sure to enable events, restarted excel etc. Still no auto update :\

Probably you are doing something wrong.. Works for me..

[cgrubb]

It worked!
I reread the part about putting the code "on the right worksheet module" and after trying all permutations, found one that did the trick.
Specifically, the code you suggested goes in the code list for the actual sheet and the code I posted goes in a module. Not immediately logical to me, but I think I understand it now.

Thank you for you help!

[NeedForExcel]

I agree, I should have been more understandable with this

on the right worksheet module

Anyways, glad you got it working..

[cgrubb]

edit:

This is a separate issue, so I'll mark this thread as solved and start a new one.

[R1_Canuck]

When adding in the

This may sound stupid, but which module do I put this in? Im having the same issue!

[JohnTopley]

I assume this should go in Sheet1.

Right click on Sheet1 tab, "View Code" and past code into "white space".

[R1_Canuck]

Fantastic, thanks John.

[JohnTopley]

Glad to help.

[PamelaSheldon]

How can this been written to include more than one color? I have 3 colors, E1, E2, and E3, all on the same sheet.......how would it be written to include all 3? Thank you so much!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Sheet1").Calculate
Call CountCcolor(Range("A1:A50"), Range("E1"))
End Sub

[TMS]

Welcome to the forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.