Custom Format cells to add a sentence using VBA code
I need to write a macro to custom format a cell in such a way, that if someone types a number in the cell (say 10 for example), the output should be "10% of EBITDA margin". How do I do this?
Create a custom NumberFormat for the cell (Format Cells > Number > Custom), like
0.00% "of EBITDA margin"
0.00%part can be any format you want for the number. The space following it can be inside the quotation marks or before them. The quotation marks can contain any string you want, a single word or a long sentence (likely, not more than 255 characters in total, though).
The code below would format Cell A1 with the same NumberFormat shown above. The format could be modified similarly, too.
Private Sub SetCellFormat() Cells(1, 1).NumberFormat = "0.00% ""of EBITDA margin""" End Sub
Try this VBA code
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim MyRNG As Range: Set MyRNG = Range("B2:B20") 'change your required range here If Not Application.Intersect(MyRNG, Target) Is Nothing Then ActiveCell.NumberFormat = "0.00 ""of EBITDA margin""" End If End Sub