Dependent Data Validation List Default

[atypicalv]

I am stuck trying to solve this question after searching for hours and trying different code.


Using a data validation dependent list, is it possible to use code to set a default to display when the list is "activated", any time through all ranges on the sheet?


Example:

Worksheet with B4 has validation list 1, B5 & C5 each have dependent validation lists. Need it to be: if “OFF” is selected in B4, then B5 & C5 are set to “12:00 A”.


In VBA, this code works for specified cells:


Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B4").Value = "OFF" Then

Range("B5:C5").Value = "12:00 A"

End If

End Sub


Here’s the catch – I need a way to have this function fire any time the data validation lists in multiple ranges in the sheet are set to “OFF”. i.e. in ranges of B4,B7,B10,B13-B55…F4,F7,F10,F13-F55…on thru to Z column, with the dependent validation lists always in the cells directly beneath.


An abbreviated example sheet can be seen here:

https://www.box.com/s/px8wkhhhxj6ftvr4wmag


Is this possible? Thanks in advance for any assistance.

 

[NARAYANK991]

Hi Jason ,


Try this :

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells(1, 1) = "OFF" Then
Target.Offset(1, 0).Resize(1, 2).Value = "12:00 A"
End If
End Sub

[/pre]
Narayan

 

[atypicalv]

Brilliant, works perfectly, thanks Narayan!