Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple one? How to shade an offset cell...
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple one? How to shade an offset cell...
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 "Kevin" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple one? How to shade an offset cell...
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 -- Regards, Tom Ogilvy "Kevin" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
simple formula I'm sure using offset? or index? | Excel Discussion (Misc queries) | |||
shade one cell that will shade multiple cells | Excel Discussion (Misc queries) | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
auto-hide rows, cell format (# and @), update cell refs, shade cel | Excel Discussion (Misc queries) | |||
Shade a cell | Excel Discussion (Misc queries) |