# Conditionally Formatting Number Format

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()

With Me.Range(“A1?)

If IsError(.Value) Then Exit Sub

If Abs(.Value) <= 1 Then
.NumberFormat = “0.00%”
Else
.NumberFormat = “#,##0.00?
End If
End With

End Sub

Posted in Uncategorized

## 7 thoughts on “Conditionally Formatting Number Format”

1. Jonathan Rynd says:

There’s a much simpler way to do this without any VBA. Set the number format of the cell to:

[<-1]#,##0.00;[<=1]0.00%;#,##0.00

It’s not as flexible as Conditional Formatting or VBA, but it’s a lot simpler.

The format is:
[Condition]Number format;[Condition]Number format;Default number format

2. Rachel says:

Can you do the same with the cell pattern? For example, if the cell’s value is 1 or less, the cell is formatted as red background. If the cell’s value is 2 or less, the cell is formatted as blue background. If the cell’s value is 3-8, a different pattern for each value. I think it’s a worksheet_calculate code, but just can’t get it to behave.

3. Brett says:

Rachel,

``` Private Sub Worksheet_Calculate() Dim MyColor As Integer MyColor = 0```

``` With Me.Range("a1") If IsError(.Value) Then Exit Sub Select Case .Value Case Is ```

4. Brett says:

Rachel,

``` Private Sub Worksheet_Calculate() Dim MyColor As Integer MyColor = 0 With Me.Range("a1") If IsError(.Value) Then Exit Sub Select Case .Value Case Is ```

5. Dick Kusleika says:

Brett: Code in the comments has a few bugs in it. Eliminate all blank lines in your code – just put a single space. Also, make sure you close the VB tag with a slash, not a backslash.

6. Brett says:
Private Sub Worksheet_Calculate()
Dim MyColor As Integer
MyColor equalsign 0
With Me.Range(“a1”)
If IsError(.Value) Then Exit Sub
Select Case .Value
Case Is lessthansign 1
MyColor equalsign 3
Case Is lessthansign 2
MyColor equalsign 5
Case 3
MyColor equalsign 7
End Select
.Interior.ColorIndex equalsign MyColor
End With
End Sub
7. Brett says:

Rachel,

The preceeding code, with the usual logic operators in place of the lessthansign and equalsign, changes the fill color of A1 based on the value of A1 when the sheet calcs. Reference A2 from A1 and enter values in A2. A1 should change fill color. You can add cases.

Brett

Posting code? Use <pre> tags for VBA and <code> tags for inline.