Search code examples
excelvba

VBA Excel two columns dependency based on the value of 1st column

I have two values in the same row. These values will be added to other rows below on the same basis. I would like to make the value from column Q dependent on the value in column P. They both use data validated as a list of values prepared. The dependency I would like to have is shown in the image below:

enter image description here

So far I tried to make it by the following code:

  Private Sub Worksheet_SelectionChange(ByVal Target As Range)


  Dim RowTarget As Range
  Dim CurrentRow As Long

  Dim cw As Worksheet
  cw = ActiveSheet

  Dim ApprovalStatus As String, RequestStatus As String

  CurrentRow = RowTarget.Row

  ApprovalStatus = cw.Range("P" & CurrentRow)

  RequestStatus = cw.Range("Q" & CurrentRow)

  Select Case ApprovalStatus.Value
  Case "APPROVED"
  RequestStatus.Value = "ACTIVE"
  Case "REJECTED"
  RequestedStatus.Value = "ABORTED"
  Case "SENT FOR APPROVAL"
  RequestedStatus.Value = "ON HOLD"
  Case "N/A"
  RequestedStatus.Value = "COMPLETED"
  End Select


  End Sub

At the line of:

    Select Case ApprovalStatus.Value

I have an error: Invalid qualifier

How could I make the 2nd value dependent on the first one?

Solution

  • I can't use Target, because it has been used in different macro

    • Target is a parameter variable in Worksheet_SelectionChange. It doesn't conflict with other sub procedures.

    • You can change the parameter variable name as needed.

    Private Sub Worksheet_SelectionChange(ByVal MyTarget As Range)
    Debug.Print MyTarget.Address
End Sub
    • Your code could be as below.
    Private Sub Worksheet_SelectionChange(ByVal RowTarget As Range)
    Dim CurrentRow As Long
    Dim ApprovalStatus As String 
    Dim RequestStatus As Range
    CurrentRow = RowTarget.Row
    ApprovalStatus = me.Range("P" & CurrentRow).Value
    Set RequestStatus = me.Range("Q" & CurrentRow)
    Application.EnableEvents = False ' You may need this line if Change event is used
    Select Case ApprovalStatus
    Case "APPROVED"
        RequestStatus.Value = "ACTIVE"
    Case "REJECTED"
        RequestedStatus.Value = "ABORTED"
    Case "SENT FOR APPROVAL"
        RequestedStatus.Value = "ON HOLD"
    Case "N/A"
        RequestedStatus.Value = "COMPLETED"
    End Select
    Application.EnableEvents = True
End Sub