Worksheet_change event to add character in front of every populated cell VBA-CodePudding

Is there a way to add a ' character before each cell that has a value on a worksheet when a worksheet_change() event triggers? For instance, if a cell is modified, I would like the code to run through every cell on the worksheet and add a ' in front of each populated cells value.

3/31/2021 converted to '3/31/2021

$56.092 converted to '$56.092

59.5% converted to '59.5%

Edit

Below is an example used to achieve this method; however, this performs slow over large datasets that may be pasted in.


Dim cell As Range

For Each cell In Target

    If cell <> "" Then
        cell.Value = "'" & cell.Value
    End If
    
Next cell

End Sub

CodePudding user response：

This will only apply formatting to the last cell edited... but as long as the existing data is good this housekeeping should be enough. you can loop the same code to correct your existing data if need be. I haven't tested it on every data type, but it works on the handful I did test.

On Sheet Module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Cells.Count = 1 Then
        If Target <> "" Then
            Target.Value = "'" & Format(Target, Target.NumberFormat)
        End If
    End If
End Sub