Conditional statements for dropdown list and cell formatting

user_15200763 0
Viewed 45

I have 3 statuses in a dropdown list in Col C. namely "N/A", "Pending" and "Completed" and a date column in Col B. If the user selects N/A from the dropdown in Col C. it locks the date field to the left and provides some formatting to the field. However, if the user selects another status besides "N/A" it clears the formatting and N/A text from date field. I have the code below which does everything I need it to, except that if a date is entered into the field and then I select "completed" or "pending" it clears the field - which it shouldn't. Please help.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Target.Count = 1 Then
        If Not Intersect(Target, Range("C3:C13,C18:C28")) Is Nothing Then
            Application.EnableEvents = False
            If Target.Value = "N/A" Then
                With Target(1, 0)
                    .Value = "(N/A)"
                    .Enabled = False
                    With .Interior
                        .Pattern = xlLightDown
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                End With
            Else
                With Target(1, 0)
                    .Value = ""
                    .Enabled = True
                    With .Interior
                        .Pattern = xlPatternNone
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                End With
            End If
            Application.EnableEvents = True
        End If
    End If
    
End Sub
conditional-statements vba excel
1 Answers

Given your description of what you want, the following code should get you there. I think it may have been how you aimed at the adjacent cell.

I'm not sure how you lock the date field as I can't see where you Protect the sheet in your code - I'll leave that part to you. Let me know how it goes.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo GetOut
Application.EnableEvents = False

If Not Intersect(Range("C3:C13,C18:C28"), Target) Is Nothing Then
    
    If Target.Text = "N/A" Then
        With Target.Offset(0, -1)
            .Value = "(N/A)"
            .Locked = True
            With .Interior
                .Pattern = xlLightDown
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End With
            Else
            With Target.Offset(0, -1)
                .Value = ""
                .Locked = False
                With .Interior
                    .Pattern = xlPatternNone
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            End With
    End If

End If

Continue:
    Application.EnableEvents = True
    Exit Sub
GetOut:
    MsgBox Err.Description
    Resume Continue

End Sub
Admin1
Related