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
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
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.
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
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
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.
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
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