Dynamic Formats

[PHMayfield]

Is it possible to have a format for % that only shows two significant digits?

In A1: 4.7123% is shown as 4.7%
Change A1: 47.123% changes to show 47% without me having to manually change anything.

Thanks for the help

[Damon Ostrander]

Hello Paul,

You can do this by adding a worksheet event macro. If the cell (A1 in the example) does not contain a formula (i.e., the data is entered manually) then use the Change event routine below. If the cell does contain a formula so that the cell value is updated when the worksheet calculates, then use the Calculate event macro below. To install one of these macros, just right-click on the worksheet's tab, select View Code, and paste the appropriate routine into the VBE code pane that appears.

Private Sub Worksheet_Calculate()
If [a1] >= 0.1 Then
[a1].NumberFormat = "0%"
Else
[a1].NumberFormat = "0.0%"
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = [a1].Address Then
If [a1] >= 0.1 Then
[a1].NumberFormat = "0%"
Else
[a1].NumberFormat = "0.0%"
End If
End If
End Sub