Hi Neil,

If, alternatively, it was your intention that the user select the cells to
colour and press the button, without a selection input box, then try
instead:

'=============
Private Sub CommandButton2_Click()
Selection.Interior.ColorIndex = 3
Me.Calculate
End Sub
'<<=============


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Neil,

Try something like:

'=============
Private Sub CommandButton1_Click()
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Please use the mouse to " _
& "select cells to be coloured", _
Title:="Colour Selector", _
Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then
rng.Interior.ColorIndex = 3
ActiveSheet.Calculate
End If
End Sub

'<<=============


PS - I have only just this afternoon learned what a UDF is, and
discovered
that I could only get it to work by placing it in a module, rather than
attaching it to a sheet's "Microsoft Excel Object". Is there a rule on
when
to include new modules? Or can I put all the UDF's into one module?


I would suggest that you always store UDF's in a standard module. Whether
you use more than one module is a matter of taste and organisation.


---
Regards,
Norman


"Neil Goldwasser" wrote in
message ...
Having read up on two fantastically informative websites about excel
colour
functions:
http://www.xldynamic.com/source/xld.ColourCounter.html
http://www.cpearson.com/excel/colors.htm

I have inserted the UDFs suggested so that the spreadsheet counts how
many
red cells there are within a certain range. However, as explained on the
sites, changing a cell's colour does not trigger a worksheet calculation,
so
if one or more cells change the result of the formula used is incorrect
until
a manual sheet recalculation.

The xldynamic website suggests "to overcome this, you need to force a
sheet
calculate. 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".

Unfortunately I do not know how to do this. Ideally I need to create a
button that will (a) turn the selected cells red and (b) then
automatically
run a sheet calculation, so that whenever cells are altered with this
button,
the formula is ALWAYS correct.

Could there also be a button included to turn all cells in the same range
back to "no fill" (i.e. clear answers so the user can start from scratch
again)?

Could anybody help this macro learner please?

Many thanks in advance, Neil Goldwasser

PS - I have only just this afternoon learned what a UDF is, and
discovered
that I could only get it to work by placing it in a module, rather than
attaching it to a sheet's "Microsoft Excel Object". Is there a rule on
when
to include new modules? Or can I put all the UDF's into one module?