PDA

View Full Version : Solved: Setting A Range In VBA



zoom38
02-13-2006, 05:28 PM
How do I update my code to only include the range B8:O34. Because it is set for the entire worksheet it is causing errors elsewhere.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Target.Value
Case "V.5", "V1" To "V999"
Target.Interior.ColorIndex = 35

Case "PL.5", "PL1" To "PL999"
Target.Interior.ColorIndex = 34

Case "SL.5", "SL1" To "SL999", "FS.5", "FS1" To "FS999"
Target.Interior.ColorIndex = 36

Case Is = "ML.5", "ML1" To "ML999"
Target.Interior.ColorIndex = 24

Case Else
'No conditions met, so make it normal
Target.Interior.ColorIndex = -4142
End Select
End Sub

Thanks
Gary

Bob Phillips
02-13-2006, 05:38 PM
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Not Intersect(Target, Sh.Range("B8:O34")) Is Nothing Then
Select Case Target.Value
Case "V.5", "V1" To "V999"
Target.Interior.ColorIndex = 35

Case "PL.5", "PL1" To "PL999"
Target.Interior.ColorIndex = 34

Case "SL.5", "SL1" To "SL999", "FS.5", "FS1" To "FS999"
Target.Interior.ColorIndex = 36

Case Is = "ML.5", "ML1" To "ML999"
Target.Interior.ColorIndex = 24

Case Else
'No conditions met, so make it normal
Target.Interior.ColorIndex = -4142
End Select
End If
End Sub

Zack Barresse
02-13-2006, 06:45 PM
Bob never leaves anything for anybody else. :( LOL!!

Also, I generally perform a check to make sure only a single cell is selected at the time (as that's the general method of entering data in my application); if this is the case with you, you may want to add this line right at the top of the procedure ...

If Target.Cells.Count > 1 Then exit sub

johnske
02-13-2006, 08:35 PM
Bob never leaves anything for anybody else. :( LOL!!... [/vba]You've noticed that too eh? :rofl: (Ya just gotta be quick on the keyboard when Bob's around - OFT's not good enough, I'll have to learn to use my other fingers for typing... :devil: )

zoom38
02-13-2006, 09:54 PM
Thanks gentlemen, so far xld's & firefytr's code change is working.