Coloring cells thru VBA

[Lawrence Mak]

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

 

[Frank Kabel]

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.

 

[Mark Rosenkrantz]

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 (e-mail address removed)

 

[Lawrence Mak]

Hi Frank,

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

Lawrence Mak

 

[Frank Kabel]

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

 

[David McRitchie]

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