VBA Enter key problem

[hatstand]

I have a cell with a validation drop down. I also have the code below that applies formatting to a range depending on the value chosen in the validation cell.

The problem is that validation enters the value. But doesn’t “Press Enter” afterwards. So the vba won’t pick up the value and run.

I’ve tried using a separate combo box, SendKeys ~, SendKeys ENTER etc but still can’t think of a way to do it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" And Target = "Dogs" Then
Range("Row18").Select
Selection.NumberFormat = "0.00%"
End If
If Target.Address = "$B$2" And Target = "Cats" Then
Range("Row18").Select
Selection.NumberFormat = "0"
End If
End Sub

 

[Ruddles]

 

[hatstand]

Just in case anyone reads this. I have now found out why the code Ruddles supplied would not work. It was due to a function I have in the workbook. The function adds together all the text from a range of cells. I have now stopped using this function and the code is working perfectly.

Function CombineTextRange(myRange As Range)

Dim cell As Range
For Each cell In myRange
If Len(cell) > 0 Then
CombineTextRange = CombineTextRange & cell.Value & " "
End If
Next cell
CombineTextRange = Left(CombineTextRange, Len(CombineTextRange) - 1)

End Function