starsky
07-17-2009, 03:30 AM
Hi,
I'm trying to set up a worksheet so that if a user selects 'Yes' from a drop down box in column D the value 'n/a' will be applied to the next 4 cells (in columns E,F,G,H) in same row row. Otherwise a small drop down list will be created in each of those next 4 cells.
So far I can make this work fine for one Target cell (D7). I would like it to work for all cells in a Target range of "D7:D1000".
With regard to applying changes to 4 offset cells, instead of 1 as below, I've run into problems when I've declared more than one range and tried to execute the code - Excel appears to freeze and there is flickering in the few cells with anything in them.
Any solutions would be wonderful.
Thanks.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg1 As Range
Set Target = Range("D7")
Set rg1 = Target.Offset(0, 1)
If Target = "Yes" Then
rg1.Value = "n/a"
Else: With rg1.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$Q$7:$Q$9"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End Sub
I'm trying to set up a worksheet so that if a user selects 'Yes' from a drop down box in column D the value 'n/a' will be applied to the next 4 cells (in columns E,F,G,H) in same row row. Otherwise a small drop down list will be created in each of those next 4 cells.
So far I can make this work fine for one Target cell (D7). I would like it to work for all cells in a Target range of "D7:D1000".
With regard to applying changes to 4 offset cells, instead of 1 as below, I've run into problems when I've declared more than one range and tried to execute the code - Excel appears to freeze and there is flickering in the few cells with anything in them.
Any solutions would be wonderful.
Thanks.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg1 As Range
Set Target = Range("D7")
Set rg1 = Target.Offset(0, 1)
If Target = "Yes" Then
rg1.Value = "n/a"
Else: With rg1.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$Q$7:$Q$9"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End Sub