Validated List Cell enters data into other cell

[jeff]

HI,

I think this might do it. right click on your sheet
tab, select view code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$3" And Target = "Unused" Then
Range("e4") = "U"
Range("E5") = "U"
Range("E6") = "U"
End If
End Sub

jeff

-----Original Message-----
I've never posted here, but read alot of the other postings.

I'm hoping for a quick/easy solution (just like everyone else I guess)

Cell E3 is a Validated LIST cell. If "Unused" is

selected from the list. I would like Excel to
automatically enter the letter "U" in cells E4,E5,and
E6. If any other selection is made from E3, then I would
like E4-E6 to accept user input.

 

[Guest]

Jeff

Thanks! That worked perfectly

Now to make it complicated....

Cell E3 and it's subsequent cells E4-E6 are just one instance

Cell X# and the three cells directly beneath (Cells X#+1, X#+2, and X#+3) all should do the same thing wherever Cell X has "unused" selected

Anyway I can make it this dynamic

(there will be approxiametly 144 Validated List Cells that should change the value for the 3 cells directly beneath throughout this worksheet

Hope I'm explaining this in a comprehensible manner.

 

[Debra Dalgleish]

The following code will clear the three cells below the data validation
cell, or enter a "U" --

'=================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If rngDV Is Nothing Then Exit Sub
If Intersect(Target, rngDV) Is Nothing Then
Exit Sub
Else
If Target.Value = "Unused" Then
Application.EnableEvents = False
Target.Offset(1, 0).Resize(3, 1) = "U"
Application.EnableEvents = True
Else
Target.Offset(1, 0).Resize(3, 1).ClearContents
Exit Sub
End If
End If
End Sub
'=================================