Using Excel’s conditional formatting (Format>Conditional Format), you can’t set the number format as one of the formatting options. To do that, you have to use an event macro, namely the Worksheet_Calculate event.
In this example, if the cell’s value is 1 or less, the cell is formatted as a percent. Otherwise the format is set to show a comma and two decimal places. The Abs() function is used to handle negative numbers and the IsError() function will avoid a run-time error if the cell contains an error such as #DIV/0.
Private Sub Worksheet_Calculate()
If IsError(.Value) Then Exit Sub
If Abs(.Value) <= 1 Then
.NumberFormat = “0.00%”
.NumberFormat = “#,##0.00?