Simple one? How to shade an offset cell...

[Kevin]

Part of my code puts a value into an offset cell;
depending on the value of that cell, I want to shade the
cell a certain colour. Is there an easier way to do this,
ideally calling another generic subroutine....here 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 will have various other offset cells (i.e. (0,61) &
(0,62) that I would like to do the same thing with...can I
build this into the first line with a one-liner somehow?

 

[Bernie Deitrick]

Kevin,

You can use a generic routine that gets a cell and value passed to it.

In this example, you can call ColorCell with the offsets of 60, 61, 62, etc.
(or in a loop), varying the passed parameter values as required.

Sub TryNow()
Dim CProdRate As Double
CProdRate = 0.8
ColorCell ActiveCell.Offset(0, 60), CProdRate
ColorCell ActiveCell.Offset(0, 61), CProdRate * 1.5
ColorCell ActiveCell.Offset(0, 62), CProdRate *.2
End Sub

Sub ColorCell(myCell As Range, myLevel As Double)
myCell.Value = myLevel
With myCell.Interior
If myLevel > 0.9 Then
.ColorIndex = 4
.Pattern = xlSolid
ElseIf myLevel > 0.75 Then
.ColorIndex = 6
.Pattern = xlSolid
ElseIf myLevel > 0.01 Then
.ColorIndex = 3
.Pattern = xlSolid
End If
End With
End Sub

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

Public Sub ColorOffset(rng as Range, col as long, cprodrate as double)
rng.Offset(0, col).Value = cprodrate
If cprodrate > 0.9 Then
With rng.Offset(0, col).Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
ElseIf cprodrate > 0.75 Then
With rng.Offset(0, col).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ElseIf cprodrate > 0.01 Then
With rng.Offset(0, col).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
End Sub

Usage

Dim lval as Long, crate as double
lval = 60
crate = .87
ColorOffset activecell, lval, crate