Dependant? Data Validation

**matrixskydiver** · 12-08-2007, 10:22 AM

That's great. Thank you for your help so far.

I have managed to do that, but realise that the example I gave in my original question was too vague.

How do I get this to apply to all of the cells in columns C, F, I, L, O, R, U, X & AA from row 7 down (for an infinate number of rows), so that the user is forced to enter the choice in the cell to the right of the one they are populating and the calculation then appearing in the cell to the right of the choice?

**mikerickson** · 12-08-2007, 03:42 PM

Good Morning!
This modification should act on the ranges you requested.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim userInput As String

If Target.Address Like "?[CFLIORU]*" Or Target.Address Like "$AA*" And _
     Target.Row >= 7 And Target.Cells.Count = 1 Then
    If IsNumeric(Target.Value) Then
    Do
       userInput = Application.InputBox("Low, Mid, High, or Firm", Default:="Mid", Type:=2)
       If userInput = "False" Then
            Rem Cancel pressed
           Application.EnableEvents = False
           Application.Undo
           Application.EnableEvents = True
           Exit Sub
       End If
    Loop Until userInput Like "[LMHFlmhf]*"
    Application.EnableEvents = False
    With Target
        .Range("b1").Value = Application.Proper(userInput)
    
        Select Case UCase(Left(userInput, 1))
            Case "L"
                .Range("c1") = Val(.Value) * 0.25
            Case "M"
                .Range("c1") = Val(.Value) * 0.5
            Case "H"
                .Range("c1") = Val(.Value) * 0.75
            Case "F"
                .Range("c1") = Val(.Value)
        End Select
    End With
    Application.EnableEvents = True
    End If
End If
End Sub