Update custom formulas automatically
 

I was kindly given some code to count the incidence of a background colour in
a range of cells. Is there any way of having the answer automatically update
as now if I change a background colour the formula does not update
automatically. I'm pressing f2 on formula and pressing return to update but I
don't want to have to do this as there are many incidences of the formula.
Any help appreciated.

Update custom formulas automatically
 

Hi there,

Format changes do not trigger any events in Excel, so there is no direct way
of capturing color change of a cell. Some of the work-arounds could be:

- to include Me.Calculate into the ChangeSelection event of the worksheet
- run ActiveSheet.Calculate, say, every second, using the OnTime method

but I wouldn't recommend that, especially given that, as you said, "there
are many incidences of the formula".

Regards,
KL


" Inserting an option button in Word"
soft.com wrote in message
...
I was kindly given some code to count the incidence of a background colour
in
a range of cells. Is there any way of having the answer automatically
update
as now if I change a background colour the formula does not update
automatically. I'm pressing f2 on formula and pressing return to update
but I
don't want to have to do this as there are many incidences of the formula.
Any help appreciated.

Update custom formulas automatically
 

maybe the UDF this not contain the following line of code at the start
of the function

Application.Volatile True

DM Unseen

Update custom formulas automatically
 

This will not force a recalculation on a colour change.

If you have to do this, and you have found it's major flaw, what I do is to
add a button to the toolbars that is used to set the colour. It provides a
colour picker, and when OKed, forces a recalculation.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DM Unseen" wrote in message
oups.com...
maybe the UDF this not contain the following line of code at the start
of the function

Application.Volatile True

DM Unseen

Update custom formulas automatically
 

Hi DM Unseen,

This won't help since, as I said, color change doesn't trigger any event
(incl. recalculation) so Application.Volatile will only fire out upon a real
change on sheet.

Regards,
KL


"DM Unseen" wrote in message
oups.com...
maybe the UDF this not contain the following line of code at the start
of the function

Application.Volatile True

DM Unseen

Update custom formulas automatically
 

KL, Bob

thanks for the update. This means a recalculation needs to be forced
(and volatile should be set) The button trick should work, and is
probably the easiest.

PS for real die-hards

Update custom formulas automatically
 

Bob, can you provide an example of that code. TIA

Greg

Update custom formulas automatically
 

Okay, but be warned, it is incredibly complex.

Sub SetCellColour()
Application.Dialogs(xlDialogPatterns).Show
ActiveSheet.Calculate
End Sub

LOL! If you are using a UDF for the colorindex, don't forget you will need
Application.Volatile, otherwise the recalc doesn't invoke it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"GregR" wrote in message
oups.com...
Bob, can you provide an example of that code. TIA

Greg

Update custom formulas automatically
 

Bob, LOL!!!! I am sure it took you all morning to write that code.
Thanks

Greg