Adding character in front of changed cell value on change

[Prevost]

Hi There. I want the character "#" to be added in front of a cell value when a value is entered. I have this code but it is a worksheet_sheet change event and I am not sure if that is the correct way to go about it.
Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim OriginalValue As Variant, NewValue As Variant
        If Target.Address = ("$E$5") Then
                OriginalValue = Range("E5").Value
                NewValue = "#" & OriginalValue
                Range("E5").Value = NewValue
        ElseIf Target.Address = ("$E$6") Then
                OriginalValue = Range("E6").Value
                NewValue = "#" & OriginalValue
                Range("E6").Value = NewValue
        End If
End Sub

 

[Gary McMaster]

You should disable/re-enable the events because making a change while in the change event causes the event to call itself recursively. That can cause huge problems. You could also use "Target" without any other supporting variables to simplify things a bit.

Gary

Private Sub Worksheet_Change(ByVal Target As Range)

If Application.Intersect(ActiveSheet.Range("E5,E6"), Target) Is Nothing Then Exit Sub

Application.EnableEvents = False
    Target.Value = "#" & Target.Value
Application.EnableEvents = True


End Sub

 

[Rick Rothstein]

Hi There. I want the character "#" to be added in front of a cell value when a value is entered.

Does the # sign have to physically be in the cell, or can it only look like it is there (the value entered by the user would remain the value in the cell)? If the latter, consider Custom Formatting the cell(s) using this Type pattern...

\#@

 

[Prevost]

Awesome, Thank you!