I have created a fairly simple function that will effectively cycle a number
from 1 to 288 then back to 0.
It works fine.
The function will be applied to around 1000 cells.

In addition to the roll oevr I now want to indicate that roll-over has
occurred by changing the fill-color of the cell.
It does not seem to work

I have copied the same cell-changing code :-

Worksheets("Scheduled Servicing").Range("G44").Interior.ColorIndex =
3
Worksheets("Scheduled Servicing").Range("g44").Interior.Pattern =
xlSolid

to a subroutine and it works fine. If I call the subroutine from the
function it does not color the cell

I am beginning to get the feeling that a function cannot change the color of
a cell even via a subroutine. Is this correct?
If so how can I cange the color of the cell? Conditional formatting is out
as the requirements for color changing may end up be very complex.

Many Thanks in advance

Andy

On Thu, 24 May 2007 14:35:31 +0100, "Andy"
<(E-Mail Removed)> wrote:

>I have created a fairly simple function that will effectively cycle a number
>from 1 to 288 then back to 0.
>It works fine.
>The function will be applied to around 1000 cells.
>
>In addition to the roll oevr I now want to indicate that roll-over has
>occurred by changing the fill-color of the cell.
>It does not seem to work
>
>I have copied the same cell-changing code :-
>
> Worksheets("Scheduled Servicing").Range("G44").Interior.ColorIndex =
>3
> Worksheets("Scheduled Servicing").Range("g44").Interior.Pattern =
>xlSolid
>
>to a subroutine and it works fine. If I call the subroutine from the
>function it does not color the cell
>
>I am beginning to get the feeling that a function cannot change the color of
>a cell even via a subroutine. Is this correct?

Yes

>If so how can I cange the color of the cell? Conditional formatting is out
>as the requirements for color changing may end up be very complex.


You might be able to use an event triggered macro -- a frequent work-around
when more than three or four conditional formats are required.

--ron

Private Sub Worksheet_Calculate()
If ActiveCell > 1 Then ActiveCell.Interior.ColorIndex = 6
End Sub
to get fancier, use a select case for multiple choices
Private Sub Worksheet_Calculate()
'If ActiveCell > 1 Then ActiveCell.Interior.ColorIndex = 6
ActiveCell.Interior.ColorIndex = 0
Select Case ActiveCell
Case Is > 10: x = 3
Case Is > 5: x = 6
'etc
Case Else
End Select
ActiveCell.Interior.ColorIndex = x
End Sub

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Andy" <(E-Mail Removed)> wrote in message
news:4655913b$(E-Mail Removed)...
>I have created a fairly simple function that will effectively cycle a
>number from 1 to 288 then back to 0.
> It works fine.
> The function will be applied to around 1000 cells.
>
> In addition to the roll oevr I now want to indicate that roll-over has
> occurred by changing the fill-color of the cell.
> It does not seem to work
>
> I have copied the same cell-changing code :-
>
> Worksheets("Scheduled Servicing").Range("G44").Interior.ColorIndex
> = 3
> Worksheets("Scheduled Servicing").Range("g44").Interior.Pattern =
> xlSolid
>
> to a subroutine and it works fine. If I call the subroutine from the
> function it does not color the cell
>
> I am beginning to get the feeling that a function cannot change the color
> of a cell even via a subroutine. Is this correct?
> If so how can I cange the color of the cell? Conditional formatting is out
> as the requirements for color changing may end up be very complex.
>
> Many Thanks in advance
>
> Andy
>