I was working on a function that calculates the average
value of a range of cells. If the average amounts to
zero, I want to change either the cell background to a
particular color to alert users. I tried this in VBA:

If Occupancy = 0 Then
ActiveCell.Interior.Color= RGB(255, 0, 0)
End If

Where Occupancy is the name of the function. However this
doesn't work as expected and the cell's color remains
unchanged. Yet the same command works perfectly through
the immediate window. Can anyone explain why it doesn't
work and how to rectify it? Thanks

Hi
if this code is within a user defined function it won't work. Within
UDFs you cannot change the Excel environment (that is change the
format, etc).
You can only return values.

--
Regards
Frank Kabel
Frankfurt, Germany

Lawrence Mak wrote:
I was working on a function that calculates the average
value of a range of cells. If the average amounts to
zero, I want to change either the cell background to a
particular color to alert users. I tried this in VBA:

If Occupancy = 0 Then
ActiveCell.Interior.Color= RGB(255, 0, 0)
End If

Where Occupancy is the name of the function. However this
doesn't work as expected and the cell's color remains
unchanged. Yet the same command works perfectly through
the immediate window. Can anyone explain why it doesn't
work and how to rectify it? Thanks

Lawrence;

Use Conditional Formatting instead of VBA.
When you have 3 or less conditions ( in your case colours) that fitts best.

Choose from the Menu :

Format | Conditional Formatting

and add up to 3 conditions .
Succes;

Mark.

PS: Check my site for a more difficult approach on different colours for
cells through VBA.
www.rosenkrantz.nl Excellent Files | Downloads |
Tools | Availability

More Excel ? www.rosenkrantz.nl or
--------------------------------------------------------------------


"Lawrence Mak" wrote in message
...
I was working on a function that calculates the average
value of a range of cells. If the average amounts to
zero, I want to change either the cell background to a
particular color to alert users. I tried this in VBA:

If Occupancy = 0 Then
ActiveCell.Interior.Color= RGB(255, 0, 0)
End If

Where Occupancy is the name of the function. However this
doesn't work as expected and the cell's color remains
unchanged. Yet the same command works perfectly through
the immediate window. Can anyone explain why it doesn't
work and how to rectify it? Thanks

Hi Frank,

Thanks for the advice. Do you think there are any
workarounds?

Lawrence Mak
-----Original Message-----
Hi
if this code is within a user defined function it won't
work. Within
UDFs you cannot change the Excel environment (that is
change the
format, etc).
You can only return values.

--
Regards
Frank Kabel
Frankfurt, Germany

Lawrence Mak wrote:
I was working on a function that calculates the average
value of a range of cells. If the average amounts to
zero, I want to change either the cell background to a
particular color to alert users. I tried this in VBA:

If Occupancy = 0 Then
ActiveCell.Interior.Color= RGB(255, 0, 0)
End If

Where Occupancy is the name of the function. However
this
doesn't work as expected and the cell's color remains
unchanged. Yet the same command works perfectly through
the immediate window. Can anyone explain why it doesn't
work and how to rectify it? Thanks

.

Hi
one way as suggested by Mark: use conditional format
another way: You may use the worksheet change event. Something like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
Application.EnableEvents = False
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3
Case 2: .Inerior.ColorIndex = 10
'etc. add additional conditions
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

I would recommend Mark's solution as it requires no VBA

--
Regards
Frank Kabel
Frankfurt, Germany

Lawrence Mak wrote:
Hi Frank,

Thanks for the advice. Do you think there are any
workarounds?

Lawrence Mak
-----Original Message-----
Hi
if this code is within a user defined function it won't work. Within
UDFs you cannot change the Excel environment (that is change the
format, etc).
You can only return values.

--
Regards
Frank Kabel
Frankfurt, Germany

Lawrence Mak wrote:
I was working on a function that calculates the average
value of a range of cells. If the average amounts to
zero, I want to change either the cell background to a
particular color to alert users. I tried this in VBA:

If Occupancy = 0 Then
ActiveCell.Interior.Color= RGB(255, 0, 0)
End If

Where Occupancy is the name of the function. However this
doesn't work as expected and the cell's color remains
unchanged. Yet the same command works perfectly through
the immediate window. Can anyone explain why it doesn't
work and how to rectify it? Thanks

.

Hi Lawrence,
I didn't really look at your code but no one actually mentioned that your
use of RGB values does not work very well in Excel unless you choose
something that happens to exactly match something in your color palette.
Excel will choose the colorindex value that it things is the closest match
which is hardly the case. You will note that Frank's suggestion uses
the colorindex. Mark's solution for Conditional Formatting uses the
color palette so it that works you avoid the problem because you
specifically directly from the color palette.

Color Palette and the 56 Excel ColorIndex Colors
http://www.mvps.org/dmcritchie/excel/colors.htm

The colorindex values are listed throughout, the above page, but you can
see them in the VBE HELP (not the Excel Help)
In XL97/XL2000 VBE HELP (Alt+F11, F1) -- index -- ColorIndex property
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Frank Kabel" wrote in message ...
Hi
one way as suggested by Mark: use conditional format
another way: You may use the worksheet change event. Something like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
Application.EnableEvents = False
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3
Case 2: .Inerior.ColorIndex = 10
'etc. add additional conditions
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

I would recommend Mark's solution as it requires no VBA

--
Regards
Frank Kabel
Frankfurt, Germany

Lawrence Mak wrote:
Hi Frank,

Thanks for the advice. Do you think there are any
workarounds?

Lawrence Mak
-----Original Message-----
Hi
if this code is within a user defined function it won't work. Within
UDFs you cannot change the Excel environment (that is change the
format, etc).
You can only return values.

--
Regards
Frank Kabel
Frankfurt, Germany

Lawrence Mak wrote:
I was working on a function that calculates the average
value of a range of cells. If the average amounts to
zero, I want to change either the cell background to a
particular color to alert users. I tried this in VBA:

If Occupancy = 0 Then
ActiveCell.Interior.Color= RGB(255, 0, 0)
End If

Where Occupancy is the name of the function. However this
doesn't work as expected and the cell's color remains
unchanged. Yet the same command works perfectly through
the immediate window. Can anyone explain why it doesn't
work and how to rectify it? Thanks

.