CODEWITHSUNDEEP
vbaexcel
Bbamber
Bbamber

Reputation: 11

VBA - Cell changes

I need for users to insert yes / no into column C. If no, the next cell should display N/A and fill in as grey. If yes, the next cell should be highlighted as yellow and allow users to fill in that cell.

The code is below, but if a cell has no and then is changed to yes the next cell over doesn't change from N/A to highlighted.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    If Target.Column = 3 Then
        Set Cell = Target.Offset(0, 1)
        If Len(Target.Value) = 0 Then
            Cell.Validation.Delete
            Cell.Value = vbNullString
        Else
            If Target.Value = "Yes" Then
                With Cell.Validation
                    Cell.Interior.ColorIndex = 36
                End With
            ElseIf Target.Value = "No" Then
                Cell.Validation.Delete
                Cell.Value = "N/A"
            Else
                MsgBox "Input only Yes or No."
                Target.ClearContents
                Cell.Validation.Delete
            End If
        End If
    End If
    End Sub

Upvotes: 1

Views: 188

Answers (3)

user6432984
user6432984

Reputation:

  • You'll need to turn EnableEvents while you clear the contents of the cell. So that Worksheet_Change isn't fired again. Otherwise you'll get stuck in a loop.
  • You are not using Excel's built in Cell Validation you are making you validation

  • What you want to do can be and should be done using Excel's built in Cell Validation and Conditional Formatting.

    • This method will fail if the user affects the cell value of more than 1 cell, Cell Validation and Conditional Formatting will still work properly.

    Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range

    If Target.Column = 3 Then
    Application.EnableEvents = False
    Set Cell = Target.Offset(0, 1)
    With Cell.Interior
        If Target.Value = "Yes" Then
            'Change Cell Color to Yellow
            .ColorIndex = 36
        ElseIf Target.Value = "No" Then
            'Change Cell Color to Grey 
            ' Insert Grey Color Change
            Cell.Value = "N/A"
        Else
            Target.ClearContents
            Cell.ClearContents
            With Cell.Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
            MsgBox "Input only Yes or No."
        End If
    End With
    Application.EnableEvents = True
End If

    End Sub

You can use the MsgBox to ask if they want the value to be yes or no.

iResponse = MsgBox("Input only Yes or No.", vbYesNoCancel)
Select Case iResponse
    Case vbYes
        Target.Value = "Yes"
    Case vbNo
        Target.Value = "Yes"
    Case Else
        'You need to turn of Events when clearing the cell
        'So that the Worksheet_Change won't fire again
        Application.EnableEvents = False
        Target.ClearContents
        Cell.ClearContents

End Select

Upvotes: 0

Rosetta
Rosetta

Reputation: 2725

try

If target.value = "yes" then
    cell.validation.delete
    cell.interior.color = vbyellow
    cell.clearcontents
end if

Upvotes: 0

Yarnspinner
Yarnspinner

Reputation: 882

This is because you didn add a line in the if block for the yes condition to change the value of the neighboring cell. Also, for the no block, you might want to change the color of the cell back to white otherwise it will stay yellow once it has been yes before. The below code should achieve what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    If Target.Column = 3 Then
        Set Cell = Target.Offset(0, 1)
        If Len(Target.Value) = 0 Then
            Cell.Validation.Delete
            Cell.Value = vbNullString
        Else
            If Target.Value = "Yes" Then
                With Cell.Validation
                    Cell.Interior.ColorIndex = 36
                End With
                Cell.Value = ""
            ElseIf Target.Value = "No" Then
                Cell.Validation.Delete
                Cell.Value = "N/A"
                Cell.Interior.ColorIndex = 0
            Else
                MsgBox "Input only Yes or No."
                Target.ClearContents
                Cell.Validation.Delete
            End If
        End If
    End If
    End Sub

Upvotes: 1

Related Questions