1

I am trying to set up a worksheet to automatically change formatting as the user updates a drop down or types in the selection type. My current build is functional, but I noticed that if I type in my selection and hit enter, the target function picks up the row below it.

Column 3 has a drop down box where you can select either number or %, and this tells the code to format the row as such. However if I type the identifier into row 3 and hit enter, the target function happens after the enter press, and changes row 4 with row 3's identifier.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value = "" Then Exit Sub
If (Target.Column <> 3) Then Exit Sub
Application.EnableEvents = False

If Target.Value = "%" Then
Application.EnableEvents = True
    ActiveCell.EntireRow.Select
    Selection.NumberFormat = "0.0%"

ElseIf Target.Value = "Number" Then
Application.EnableEvents = True
    ActiveCell.EntireRow.Select
    Selection.NumberFormat = "0"
End If
Application.EnableEvents = True
Target.Select
End Sub

I would like to figure out how to dynamically deal with the inputs if the user types the data into the field and uses enter, rather than the drop down. Any ideas?

CObert
  • 36
  • 3
  • 3
    `ActiveCell` refers to the selected cell - thus, when you press enter you change your active cell from `Target` to the cell below. You might get some luck changing your use of `ActiveCell` to `Target`, though I can't test it right now. You also shouldn't need to use `Select`, and it'll speed the code up if you remove it. – asongtoruin May 26 '16 at 15:25
  • 1
    See [this page](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) on how to avoid using `.Select`. – BruceWayne May 26 '16 at 15:33
  • Awesome, that solved my issue right away. Thanks so much for the tips, I will try to look at speeding up the code. Right now the database is small enough so it doesn't matter, but I can see the usefulness. – CObert May 26 '16 at 15:48

0 Answers0