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?

2 answers

  • answered 2019-03-02 02:36 Variatus

    Create a custom NumberFormat for the cell (Format Cells > Number > Custom), like

    0.00% "of EBITDA margin"
    

    The 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
    

  • answered 2019-03-02 06:38 Gaurav Jain

    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