Need help - how to shade an offset cell....(simple?)

[Kevin]

I am putting a value into an offset cell. Then, based on
that value, I want to colour the cell red, green, or
yellow. Is there an easier way to do this, maybe by
calling another subroutine? I would want to do this for
several offset cells....this is what I have now:

ActiveCell.Offset(0, 60).Value = cprodrate
If cprodrate > 0.9 Then
With ActiveCell.Offset(0, 60).Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
ElseIf cprodrate > 0.75 Then
With ActiveCell.Offset(0, 60).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ElseIf cprodrate > 0.01 Then
With ActiveCell.Offset(0, 60).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If


I want to repeat the above for the following:

ActiveCell.Offset(0, 58).Value = aprodrate
'color cell depending on value
ActiveCell.Offset(0, 59).Value = bprodrate
'color cell depending on value


Any suggestions?

 

[NickHK]

Kevin,
Conditional Formatting.

NickHK

 

[Bob Flanagan]

You could try a subroutine like the following (untested, but should work):

Sub SetACell(N as integer, V as double)
dim C as integer
C = xlNone
if V >0.9 then C = 4
if V > 0.75 then C = 6
if V > 0.01 then C = 3
with Activecell.offset(0, N)
.value = V
.colorindex = C
end with
end sub

to call:

Set A cell 60, .456

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel