Percentage problem

[ecawilkinson]

Hi,

I have not used Excel in a few years and am having a problem displaying a percentage correctly in a column of figures. I want cells that will show the percentage exactly as it is, no matter how many decimal places, e.g. if the cell content is 5 then I want to see 5%. if it is 5.1, I want to see 5.1%, if it is 5.12, then I want to see 5.12%.

thank you,
Chris

 

[Joe4]

Re: Percentage Problem

Let's say that these percentages were being entered in column C on your sheet. Here is some Event Procedure VBA code that would automatically fix those percentages the way you want as they are entered into column C:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim cell As Range
    Dim numDec As Long
    Dim numFormat As String

'   Only apply on column C
    Set myRange = Intersect(Target, Range("C:C"))
    
'   Check to see if any updated cells fall into specified range
    If Not myRange Is Nothing Then
'       Disable events so this code doesn't call itself while running
        Application.EnableEvents = False
'       Loop through all cells
        For Each cell In myRange
'           Exit sub if not numeric entry
            If Not IsNumeric(cell) Then Exit Sub
'           Count the number of numbers after the decimal
            If InStr(cell, ".") = 0 Then
                numFormat = "0%"
            Else
                numDec = Len(cell) - InStr(cell, ".")
'               Build cell format
                numFormat = "0." & String(numDec, "0") & "%"
            End If
'           ***DIVIDE ENTRY BY 100 TO CONVERT TO PERCENTAGE***
            cell = cell / 100
'           Apply format to cell
            cell.NumberFormat = numFormat
        Next cell
'       Re-enable events so this code doesn't call itself while running
        Application.EnableEvents = True
    End If

End Sub