R
RGA
Hello,
I'm having a problem with the code below. When I make a change to the
worksheet it is calling 'RetailZonesFormat' and returning values from
the variables but it won't select the cells or colour the interior.
Thanks for any help you can provide.
-Ron
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim varRtl As Variant
If Target.Column = 16 And Target.Row > 15 Then
On Error GoTo NoRtl
varRtl = Sheets("Dropdown
Lists").Range("ZONE_GROUP_ID").Find(Target, , xlValues,
xlWhole).Offset(0, -1).Value
Call RetailZonesFormat
Range("P14").Select
On Error Resume Next
Target.ClearComments
On Error GoTo 0
Target.AddComment.Text varRtl
Call EnEv
Application.CalculateFull
Exit Sub
NoRtl:
Call DisEv
Target = ""
On Error Resume Next
Target.ClearComments
On Error GoTo 0
Call EnEv
Application.CalculateFull
End
End If
End Sub
Sub RetailZonesFormat()
Dim varRtlZne As Variant
Dim varRtlVal As Variant
Dim dblRow As Double
dblRow = ActiveCell.Row
varRtlZne = Cells(dblRow, 16)
varRtlVal = Sheets("Dropdown
Lists").Range("ZONE_GROUP_ID_DETAIL").Find(varRtlZne, , _
xlValues, xlWhole).Offset(0, 1).Value
Range("R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP15,BQ15,BR15,BS15").
_
Interior.ColorIndex = xlNone
Select Case varRtlVal
Case 1
Range("Q15").Select
Case 2
Range("Q15,R15").Select
Case 3
Range("Q15,R15,S15").Select
Case 4
Range("Q15,R15,S15,T15").Select
Case 5
Range("Q15,R15,S15,T15,U15").Select
Case 6
Range("Q15,R15,S15,T15,U15,BJ15").Select
Case 7
Range("Q15,R15,S15,T15,U15,BJ15,BK15").Select
Case 8
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15").Select
Case 9
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15").Select
Case 10
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15").Select
Case 11
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15").Select
Case 12
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP15").Select
Case 13
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP15,BQ15").Select
Case 14
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP15,BQ15,BR15").Select
Case 15
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP15,BQ15,BR15,BS15").Select
End Select
With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With
Cells(dblRow, 16).Select
End Sub
I'm having a problem with the code below. When I make a change to the
worksheet it is calling 'RetailZonesFormat' and returning values from
the variables but it won't select the cells or colour the interior.
Thanks for any help you can provide.
-Ron
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim varRtl As Variant
If Target.Column = 16 And Target.Row > 15 Then
On Error GoTo NoRtl
varRtl = Sheets("Dropdown
Lists").Range("ZONE_GROUP_ID").Find(Target, , xlValues,
xlWhole).Offset(0, -1).Value
Call RetailZonesFormat
Range("P14").Select
On Error Resume Next
Target.ClearComments
On Error GoTo 0
Target.AddComment.Text varRtl
Call EnEv
Application.CalculateFull
Exit Sub
NoRtl:
Call DisEv
Target = ""
On Error Resume Next
Target.ClearComments
On Error GoTo 0
Call EnEv
Application.CalculateFull
End
End If
End Sub
Sub RetailZonesFormat()
Dim varRtlZne As Variant
Dim varRtlVal As Variant
Dim dblRow As Double
dblRow = ActiveCell.Row
varRtlZne = Cells(dblRow, 16)
varRtlVal = Sheets("Dropdown
Lists").Range("ZONE_GROUP_ID_DETAIL").Find(varRtlZne, , _
xlValues, xlWhole).Offset(0, 1).Value
Range("R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP15,BQ15,BR15,BS15").
_
Interior.ColorIndex = xlNone
Select Case varRtlVal
Case 1
Range("Q15").Select
Case 2
Range("Q15,R15").Select
Case 3
Range("Q15,R15,S15").Select
Case 4
Range("Q15,R15,S15,T15").Select
Case 5
Range("Q15,R15,S15,T15,U15").Select
Case 6
Range("Q15,R15,S15,T15,U15,BJ15").Select
Case 7
Range("Q15,R15,S15,T15,U15,BJ15,BK15").Select
Case 8
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15").Select
Case 9
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15").Select
Case 10
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15").Select
Case 11
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15").Select
Case 12
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP15").Select
Case 13
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP15,BQ15").Select
Case 14
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP15,BQ15,BR15").Select
Case 15
Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP15,BQ15,BR15,BS15").Select
End Select
With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With
Cells(dblRow, 16).Select
End Sub