Roedd <<Barb Reinhardt wedi ysgrifennu:

I just found that UDF's are not able to do what I want to do. It
would have been so easy if they could.

Oh well.


Here's a post made recently to another group. It doesn't deal with colour
formatting, but with number formats, but the principle still applies:

This idea comes up from time to time and its origins are in the way that
excel responds to the user entering a worksheet function such as NOW(). Try
it. Excel not only returns the current date and time, but also reformats the
cell. This leads some people to think that a) the formattring is being done
by the function and b) it might be possible to do something similar with a
UDF. In fact, the function is not doing the formatting. Excel is registering
that the function has been entered and is responding afterwards by
'helpfully' changing the number format. An internal list, to which we have
no access, is maintained of the functions which Excel thinks might benefit
from such reformatting.

Maybe this can be done with a global sheet change event hook. We need to
check if any cell changing was unformatted ("General") before we entered our
UDF. If it is, we change the formatting to however we want it. If the UDF is
in an add-in, the whole thing can be encapsulated within the add-in
workbook.

Say we have a function called MyNow in a regular module in an Add-In:

Option Explicit

Function MyNow()
Application.Volatile
MyNow = Now()
End Function

Now (ahem!) we put the following event code in the add-in's Thisworkbook
module:

Option Explicit

Private WithEvents oApp As Excel.Application

Private Sub Workbook_Open()
Set oApp = Application
End Sub

Private Sub oApp_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If Target.NumberFormat = "General" And _
UCase(Target.Formula) Like "=MYNOW(*)" Then _
Target.NumberFormat = "mm:ss"
End Sub

This should do what we want subject to further testing (which I'm not going
to do since I don't have any use for this!).

Rob