Need help in VBA excel on validation

deepunair84296 · July 10th, 2008, 05:00 AM

Hi Experts

I have a problem. I have 2 columns. The Column 1 is a Drop down with the values as Estimate, Realise and Deploy. I want when a user selects Estimate in Column 1 the value in column 2 should automatically change to Open and when a user selects Realise in Column 1 the value in column 2 should automatically change to In Progress and when a user selects Deploy in Column 1 the value in column 2 should automatically change to Closed.

These validations need to go on till the end of the page (i.e 1 to 65536)

Please help me to get this done.

PS: i am not a programmer.

Regards

Deepu

allenm · July 11th, 2008, 04:13 PM

Go into code for the spreadsheet that contains the columns.
The code below changes value in column B to value in column C for the row change occurred in.
--------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

'Changes value in column C when corresponding value for row in column B changes
  If Target.Column = 1 Then
    If Target.Value = "Estimate" Then Cells(Target.Row, 2).Value = "Open"
    If Target.Value = "Realize" Then Cells(Target.Row, 2).Value = "In Progress"
    If Target.Value = "Deploy" Then Cells(Target.Row, 2).Value = "Closed"
  End If

End Sub
-------------------------------------

Hope this helps.

stanleydgromjr · July 11th, 2008, 04:23 PM

deepunair84296,

allenm, nicely done.

Another solution:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    Select Case Target.Value
        Case ""
            Target.Offset(, 1).Value = ""
        Case "Estimate"
            Target.Offset(, 1).Value = "Open"
        Case "Realise"
            Target.Offset(, 1).Value = "In Progress"
        Case "Deploy"
            Target.Offset(, 1).Value = "Closed"
        Case Else
            Target.Offset(, 1).Value = ""
    End Select
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.