Changing cell color from a function

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

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
 
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.
Click to expand...


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
 
Back
Top