Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change problem
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(varRtlZ ne, , _ xlValues, xlWhole).Offset(0, 1).Value Range("R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO 15,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").S elect Case 10 Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN1 5").Select Case 11 Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN1 5,BO15").Select Case 12 Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN1 5,BO15,BP15").Select Case 13 Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN1 5,BO15,BP15,BQ15").Select Case 14 Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN1 5,BO15,BP15,BQ15,BR15").Select Case 15 Range("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN1 5,BO15,BP15,BQ15,BR15,BS15").Select End Select With Selection.Interior .ColorIndex = 39 .Pattern = xlSolid End With Cells(dblRow, 16).Select End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Worksheet_Change | New Users to Excel | |||
Worksheet_Change - NEW to VBA | Excel Worksheet Functions | |||
Problem with function "Worksheet_Change" | Excel Worksheet Functions | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |