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
Jonathan Rynd:
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:
22 June 2004, 11:18 am[Condition]Number format;[Condition]Number format;Default number format
Rachel:
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.
10 December 2007, 1:40 pmBrett:
Rachel,
[VB]
Private Sub Worksheet_Calculate()
Dim MyColor As Integer
MyColor = 0
With Me.Range(”a1″)
If IsError(.Value) Then Exit Sub
Select Case .Value
11 December 2007, 7:32 pmCase Is
Brett:
Rachel,
[VB]
Private Sub Worksheet_Calculate()
Dim MyColor As Integer
MyColor = 0
With Me.Range(”a1″)
If IsError(.Value) Then Exit Sub
Select Case .Value
11 December 2007, 7:36 pmCase Is
Dick Kusleika:
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.
12 December 2007, 9:52 amBrett:
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
Brett:
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
13 December 2007, 6:32 am