Validation on drop down and Macro to add Wrong

Hi

I have a worksheet that has drop down selection validation from list (Keep, Delete and amend).

I have another cell that has validation from list (Wrong, Complete or In Progress).

If the User selects Wrong I would like the cell with the (Keep, Delete and amend) to show Wrong but I can’t as the validation on the list has not got Wrong listed and displays Invalid. Can this be done?

You can do it using the sheet change event...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Column = 1 And Target.Value = "Wrong" Then
  Application.EnableEvents = False
  Target.Offset(0, 1).Value = "Wrong"
  Application.EnableEvents = True
End If

End Sub

You'll have to replace the Column= value with whatever column number your first drop down is in and the offset(0,1) needs the 1 changing to however many columns right of the first drop-down your second drop-down is.

Other than that it should be fine, and will ride roughshod over any validation you have on that cell.

Hi Andrew-R,

I added the Column = 8 which is column H and the offset as (0, -1) and nothing.
Also added the code to the sheet module
Am I doing something wrong?

The code should be in the workbook module, so in the VB editor ...

1. Double-click on the "This Workbook" item in the left-hand pane

2. Just to be on the safe side select "Workbook" from the left-hand drop-down in the main code window and "SheetChange" from the right-hand drop-down.

3. This will put the sub declaration and the end sub in place for you, so you just need to paste the rest of my code between the 2.

If that doesn't work then check that the "Wrong" drop-down is exactly "Wrong" and not " Wrong" or "Wrong " (with leading/trailing spaces).