Anomandaris
05-28-2009, 03:15 AM
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
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