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
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