Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Worksheet_Change problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
RGA RGA is offline
external usenet poster
  
Posts: 4
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 78
Default Worksheet_Change problem
You need to avoid changing your selection whilst
processing a cell Change

Sub RetailZonesFormat()
Dim varRtlZne As Range
Dim varRtlVal As Variant

varRtlVal = Sheets("Dropdown
Lists").Range("ZONE_GROUP_ID_DETAIL").Find
(ActiveCell.Value, , _
xlValues, xlWhole).Offset(0, 1).Value

Range("R15:U15,BJ15:BS15").Interior.ColorIndex = xlNone

If varRtlVal < 6 Then
Set varRtlZne = Range("Q15").Resize(1, varRtlVal)
Else
Set varRtlZne = Application.Intersect(Range
("Q15:U15"), Range("BJ15").Resize(1, varRtlVal - 5))
End If

With varRtlZne.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With
set varRtlZne = Nothing

End sub



-----Original Message-----
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(varRtl Zne, , _
xlValues, xlWhole).Offset(0, 1).Value
Range
("R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP 15,BQ15,B
R15,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").Sel ect
Case 10
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15").S elect
Case 11
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5").Selec
t
Case 12
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15").
Select
Case 13
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15").Select
Case 14
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15,BR15").Select
Case 15
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15,BR15,BS15").Select
End Select

With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With

Cells(dblRow, 16).Select

End Sub
.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
RGA RGA is offline
external usenet poster
  
Posts: 4
Default Worksheet_Change problem
Thanks for the quick reply Kevin. But it must be something else.

Even this line is not working:
Range("R15:U15,BJ15:BS15").Interior.ColorIndex = xlNone

No error, it just doesn't do anything. :@ I'm stumped.

Thanks again.

-Ron


"Kevin Beckham" wrote in message ...
You need to avoid changing your selection whilst
processing a cell Change

Sub RetailZonesFormat()
Dim varRtlZne As Range
Dim varRtlVal As Variant

varRtlVal = Sheets("Dropdown
Lists").Range("ZONE_GROUP_ID_DETAIL").Find
(ActiveCell.Value, , _
xlValues, xlWhole).Offset(0, 1).Value

Range("R15:U15,BJ15:BS15").Interior.ColorIndex = xlNone

If varRtlVal < 6 Then
Set varRtlZne = Range("Q15").Resize(1, varRtlVal)
Else
Set varRtlZne = Application.Intersect(Range
("Q15:U15"), Range("BJ15").Resize(1, varRtlVal - 5))
End If

With varRtlZne.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With
set varRtlZne = Nothing

End sub



-----Original Message-----
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(varRtl Zne, , _
xlValues, xlWhole).Offset(0, 1).Value
Range
("R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP 15,BQ15,B
R15,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").Sel ect
Case 10
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15").S elect
Case 11
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5").Selec
t
Case 12
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15").
Select
Case 13
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15").Select
Case 14
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15,BR15").Select
Case 15
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15,BR15,BS15").Select
End Select

With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With

Cells(dblRow, 16).Select

End Sub
.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
RGA RGA is offline
external usenet poster
  
Posts: 4
Default Worksheet_Change problem
Hi again. Update: It works on other sheets in the workbook but not the
one I need it in. Other subs that are called from the Worksheet_Change
event are working and some of them are selecting other cells. One of
then if calling an Oracle database, bringing data into a range and
naming it then creating Data Validation dropdowns.

Very confused.

"Kevin Beckham" wrote in message ...
You need to avoid changing your selection whilst
processing a cell Change

Sub RetailZonesFormat()
Dim varRtlZne As Range
Dim varRtlVal As Variant

varRtlVal = Sheets("Dropdown
Lists").Range("ZONE_GROUP_ID_DETAIL").Find
(ActiveCell.Value, , _
xlValues, xlWhole).Offset(0, 1).Value

Range("R15:U15,BJ15:BS15").Interior.ColorIndex = xlNone

If varRtlVal < 6 Then
Set varRtlZne = Range("Q15").Resize(1, varRtlVal)
Else
Set varRtlZne = Application.Intersect(Range
("Q15:U15"), Range("BJ15").Resize(1, varRtlVal - 5))
End If

With varRtlZne.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With
set varRtlZne = Nothing

End sub



-----Original Message-----
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(varRtl Zne, , _
xlValues, xlWhole).Offset(0, 1).Value
Range
("R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP 15,BQ15,B
R15,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").Sel ect
Case 10
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15").S elect
Case 11
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5").Selec
t
Case 12
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15").
Select
Case 13
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15").Select
Case 14
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15,BR15").Select
Case 15
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15,BR15,BS15").Select
End Select

With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With

Cells(dblRow, 16).Select

End Sub
.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
RGA RGA is offline
external usenet poster
  
Posts: 4
Default Worksheet_Change problem
I have a Data Validation dropdown in the cell. The Worksheet_Change
event fires when the value is changed via the dropdown. But the code
below does not work. If I type in a valid value the code works!!! Is
there another event to run the dropdown change?

TIA

R.

(RGA) wrote in message . com...
Hi again. Update: It works on other sheets in the workbook but not the
one I need it in. Other subs that are called from the Worksheet_Change
event are working and some of them are selecting other cells. One of
then if calling an Oracle database, bringing data into a range and
naming it then creating Data Validation dropdowns.

Very confused.

"Kevin Beckham" wrote in message ...
You need to avoid changing your selection whilst
processing a cell Change

Sub RetailZonesFormat()
Dim varRtlZne As Range
Dim varRtlVal As Variant

varRtlVal = Sheets("Dropdown
Lists").Range("ZONE_GROUP_ID_DETAIL").Find
(ActiveCell.Value, , _
xlValues, xlWhole).Offset(0, 1).Value

Range("R15:U15,BJ15:BS15").Interior.ColorIndex = xlNone

If varRtlVal < 6 Then
Set varRtlZne = Range("Q15").Resize(1, varRtlVal)
Else
Set varRtlZne = Application.Intersect(Range
("Q15:U15"), Range("BJ15").Resize(1, varRtlVal - 5))
End If

With varRtlZne.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With
set varRtlZne = Nothing

End sub



-----Original Message-----
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(varRtl Zne, , _
xlValues, xlWhole).Offset(0, 1).Value
Range
("R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP 15,BQ15,B
R15,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").Sel ect
Case 10
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15").S elect
Case 11
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5").Selec
t
Case 12
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15").
Select
Case 13
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15").Select
Case 14
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15,BR15").Select
Case 15
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15,BR15,BS15").Select
End Select

With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With

Cells(dblRow, 16).Select

End Sub
.

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11
Default Worksheet_Change problem
I remember there are Event firing issues on Data Validation dropdowns with
Excel 97.
Excel 2000 onward I think has the issue resolved.


"RGA" wrote in message
om...
I have a Data Validation dropdown in the cell. The Worksheet_Change
event fires when the value is changed via the dropdown. But the code
below does not work. If I type in a valid value the code works!!! Is
there another event to run the dropdown change?

TIA

R.

(RGA) wrote in message
. com...
Hi again. Update: It works on other sheets in the workbook but not the
one I need it in. Other subs that are called from the Worksheet_Change
event are working and some of them are selecting other cells. One of
then if calling an Oracle database, bringing data into a range and
naming it then creating Data Validation dropdowns.

Very confused.

"Kevin Beckham" wrote in message
...
You need to avoid changing your selection whilst
processing a cell Change

Sub RetailZonesFormat()
Dim varRtlZne As Range
Dim varRtlVal As Variant

varRtlVal = Sheets("Dropdown
Lists").Range("ZONE_GROUP_ID_DETAIL").Find
(ActiveCell.Value, , _
xlValues, xlWhole).Offset(0, 1).Value

Range("R15:U15,BJ15:BS15").Interior.ColorIndex = xlNone

If varRtlVal < 6 Then
Set varRtlZne = Range("Q15").Resize(1, varRtlVal)
Else
Set varRtlZne = Application.Intersect(Range
("Q15:U15"), Range("BJ15").Resize(1, varRtlVal - 5))
End If

With varRtlZne.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With
set varRtlZne = Nothing

End sub



-----Original Message-----
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(varRtl Zne, , _
xlValues, xlWhole).Offset(0, 1).Value
Range
("R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO15,BP 15,BQ15,B
R15,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").Sel ect
Case 10
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15").S elect
Case 11
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5").Selec
t
Case 12
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15").
Select
Case 13
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15").Select
Case 14
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15,BR15").Select
Case 15
Range
("Q15,R15,S15,T15,U15,BJ15,BK15,BL15,BM15,BN15,BO1 5,BP15,BQ
15,BR15,BS15").Select
End Select

With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With

Cells(dblRow, 16).Select

End Sub
.



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Worksheet_Change problem
The worksheet_change event in Excel 97 only fires if the cells on the
worksheet are changed by the user or by an external link. So changing a
cell by Data Validation does not fire the event (even if the user
selects the value from a Data Validation list). As mentioned in a
previous post, this issue is resolved in Excel 2000.

I got around this problem by using the worksheet_calculate function
instead. This *does* fire when a value in a Data Validation list box is
selected.

I hope this helps.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Worksheet_Change mike_e. New Users to Excel 7 July 13th 07 01:06 AM
Worksheet_Change - NEW to VBA [email protected] Excel Worksheet Functions 1 April 26th 06 05:44 PM
Problem with function "Worksheet_Change" konpego Excel Worksheet Functions 0 June 23rd 05 05:46 AM
worksheet_change vs. calculate, and worksheet_change not running Tom Ogilvy Excel Programming 1 July 14th 03 02:51 AM
worksheet_change vs. calculate, and worksheet_change not running Ross[_5_] Excel Programming 0 July 13th 03 04:27 PM


All times are GMT +1. The time now is 09:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement