Solved: rearranging Code, separating OffsetResize

[Anomandaris]

I have this code, I'm trying to adjust it a bit.... see how the Offset and Resize function is tied to each Case's colorindex, I want to separate that. As 'Offset(-2).Resize(3)' is valid for all cases, can't I use it separately so I just have to type it once to apply to all the Cases?

thanks


For Each tcell In Rzsco
Select Case tcell.Value

Case 0 To PosDec
tcell.Offset(-2).Resize(3).Interior.ColorIndex = 36
Case PosDec To (PosDec * 2)
tcell.Offset(-2).Resize(3).Interior.ColorIndex = 6
Case (PosDec * 2) To (PosDec * 3)
tcell.Offset(-2).Resize(3).Interior.ColorIndex = 44
Case (PosDec * 3) To (PosDec * 4)
tcell.Offset(-2).Resize(3).Interior.ColorIndex = 45
Case (PosDec * 4) To (PosDec * 5)
tcell.Offset(-2).Resize(3).Interior.ColorIndex = 46
Case (PosDec * 5) To tMax
tcell.Offset(-2).Resize(3).Interior.ColorIndex = 3
Case NegDec To 0
tcell.Offset(-2).Resize(3).Interior.ColorIndex = 24
Case (NegDec * 2) To NegDec
tcell.Offset(-2).Resize(3).Interior.ColorIndex = 17
Case (NegDec * 3) To (NegDec * 2)
tcell.Offset(-2).Resize(3).Interior.ColorIndex = 41
Case (NegDec * 4) To (NegDec * 3)
tcell.Offset(-2).Resize(3).Interior.ColorIndex = 23
Case (NegDec * 5) To (NegDec * 4)
tcell.Offset(-2).Resize(3).Interior.ColorIndex = 5
Case tMin To (NegDec * 5)
tcell.Offset(-2).Resize(3).Interior.ColorIndex = 55
Case Else
tcell.Offset(-2).Resize(3).Interior.ColorIndex = 19
End Select

[Anomandaris]

this is because I want to put the
OffsetResize value into an option button, while the rest of the Color code is attached to another button

[Bob Phillips]

For Each tcell In Rzsco
Select Case tcell.Value

Case 0 To PosDec: ci = 36
Case PosDec To (PosDec * 2): ci = 6
Case (PosDec * 2) To (PosDec * 3): ci = 44
Case (PosDec * 3) To (PosDec * 4): ci = 45
Case (PosDec * 4) To (PosDec * 5): ci = 46
Case (PosDec * 5) To tMax: ci = 3
Case NegDec To 0: ci = 24
Case (NegDec * 2) To NegDec: ci = 17
Case (NegDec * 3) To (NegDec * 2): ci = 41
Case (NegDec * 4) To (NegDec * 3): ci = 23
Case (NegDec * 5) To (NegDec * 4): ci = 5
Case tMin To (NegDec * 5): ci = 55
Case Else: ci = 19
End Select

tcell.Offset(-2).Resize(3).Interior.ColorIndex = ci

[Anomandaris]

Thanks xld that works!
I used ......Dim ci
thats the correct way right?

[Bob Phillips]

Thanks xld that works!
I used ......Dim ci
thats the correct way right?

Dim ci As Long

always type it