PDA

View Full Version : Solved: Name a Range



zoom38
04-30-2006, 09:49 AM
Can someone assist me in shortening the following code. As you can see there are only 2 conditions. I tried to shorten it by naming the range but it didn't work. I tried the following but it didn't work.

if datepart("d", Range("a34")) <> 17 then set range("b8:o34") = myrange
elseif datepart("d", Range("a34")) = 17 then set range("b8:o35") = myrange
end if

I then tried substituting myrange in the following line but it did not work.
If Not Intersect(Target, Sh.Range(myrange)) Is Nothing Then




Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

' Sh.Protect Password:="pass", UserInterfaceOnly:=True
If Target.Cells.Count > 1 Then Exit Sub
If DatePart("d", Range("a34")) <> 17 Then
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 "BL.5", "BL1" To "BL999"
Target.Interior.ColorIndex = 36

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

Case "A/FX", "B/FX", "C/FX"
Target.Interior.ColorIndex = 22

Case Else
'No conditions met, so make it normal
Target.Interior.ColorIndex = -4142
End Select
End If
ElseIf DatePart("d", Range("a34")) = 17 Then
If Not Intersect(Target, Sh.Range("b8:o35")) 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 "BL.5", "BL1" To "BL999"
Target.Interior.ColorIndex = 36

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

Case "A/FX", "B/FX", "C/FX"
Target.Interior.ColorIndex = 22

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


What am I missing?
Thanks
Gary

lenze
04-30-2006, 01:34 PM
Shouldn't this

range("b8:o34") = myrange
be

myrange = Range("B8:O34")

lenze

zoom38
04-30-2006, 04:16 PM
Lenze I made the change and came up with the following but it doesn't work. I get this error message:
" Run-time error '1004':
Application-defined or object-defined error"

It then highlights this line:
If Not Intersect(Target, Sh.Range(myrange)) Is Nothing Then


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Sh.Protect Password:="pass", UserInterfaceOnly:=True

If DatePart("d", Range("a34")) <> 17 Then Set myrange = Range("b8:o34")
If DatePart("d", Range("a34")) = 17 Then Set myrange = Range("b8:o35")

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Sh.Range(myrange)) 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 "BL.5", "BL1" To "BL999"
Target.Interior.ColorIndex = 36

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

Case "A/FX", "B/FX", "C/FX"
Target.Interior.ColorIndex = 22

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


Any ideas?
Thanks
Gary

Bob Phillips
05-01-2006, 02:59 AM
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myRange As Range

' Sh.Protect Password:="pass", UserInterfaceOnly:=True

If DatePart("d", Range("A34")) <> 17 Then Set myRange = Range("B8:O34")
If DatePart("d", Range("A34")) = 17 Then Set myRange = Range("B8:O35")

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, myRange) 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 "BL.5", "BL1" To "BL999"
Target.Interior.ColorIndex = 36

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

Case "A/FX", "B/FX", "C/FX"
Target.Interior.ColorIndex = 22

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

zoom38
05-01-2006, 07:28 AM
Thanks xld you did it. Please mark this solved.
Gary