Formatting for variable decimals

Penfold

New Member
Joined
Mar 4, 2002
Messages
43
I know with conditional formatting you can change colours and fonts, but is there a way of changing the number of figures after the decimal point. For example, if my number is less than 100 I want two significant figures. If it is greater than 100 but less than 1000 I want one, and for greater than 1000 I don't want anything after the decimal point:
eg 99.99
999.9
9999

Thanks,

Penfold
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Hi Penfold

Don't think you can achieve this with conditional formatting. You could try this event macro though:
Right click your sheet tab, left click View Code, then paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value< 100 Then
Target.NumberFormat = "0.00"
Else
If Target.Value< 1000 Then
Target.NumberFormat = "0.0"
Else
If Target.Value >= 1000 Then
Target.NumberFormat = "0"
End If
End If
End If
End Sub

This appears to round up (ie 99.995 becomes 100.00) but may help you.

regards
Derek
This message was edited by Derek on 2002-03-11 00:51
 
Upvote 0
Hi


I was beaten to it but here is mine anyway.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strFormat As String
If Target.Cells.Count > 1 Then End
If Not IsNumeric(Target) Then End

Select Case Target
Case Is < 100
strFormat = "0.00"
Case Is < 1000
strFormat = "0.0"
Case Is > 9999
strFormat = "0"
End Select

Target.NumberFormat = strFormat

End Sub
 
Upvote 0
Thanks Derek, thats almost exactly what I want except that I have a large range of cells which I want to apply this to (rather than one by one). I guess I can use a similar process via VBA to do this or alternatively add an IF(a1<100,trunc(a1,2)...etc) statement to each cell but I was hoping that the magic of conditional formatting would solve all of my problems.

Cheers,

Penfold
 
Upvote 0
Hi again

Paste this code into a standard macro module, select the area you want to change then run this macro. This will format all your existing cells. After this the event macro I gave you before (or the one from Dave Hawley) will format any new entries you make, as you make them.

For Each cell In Selection
If cell.Value < 100 Then
cell.NumberFormat = "0.00"
Else
If cell.Value < 1000 Then
cell.NumberFormat = "0.0"
Else
If cell.Value >= 1000 Then
cell.NumberFormat = "0"
End If
End If
End If
Next
End Sub

regards
Derek
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top