Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help - how to shade an offset cell....(simple?)
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help - how to shade an offset cell....(simple?)
Kevin,
Conditional Formatting. NickHK "Kevin" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help - how to shade an offset cell....(simple?)
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 "Kevin" wrote in message ... 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? |
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) | |||
Simple one? How to shade an offset cell... | Excel Programming |