Nested If statement to control format of number-type cells
 

The conditional formatting in Excel doesn't seem to allow for numeric
formatting. I need to control the formatting of numeric cells as shown below-
Please help.

0.00# = 4 decimals
0.0# = 3 decimals
0.## = 2 decimals
1 to 9.99 = 2 decimals
10 to 99.9 = 1 decimal
100 plus = no decimals
--
TB

Nested If statement to control format of number-type cells
 

On Wed, 14 May 2008 17:28:00 -0700, TB wrote:

The conditional formatting in Excel doesn't seem to allow for numeric
formatting. I need to control the formatting of numeric cells as shown below-
Please help.

0.00# = 4 decimals
0.0# = 3 decimals
0.## = 2 decimals
1 to 9.99 = 2 decimals
10 to 99.9 = 1 decimal
100 plus = no decimals

You will need to either use a macro, perhaps and event-triggered one; or use
Conditional Formatting in Excel 2007, which does permit number formatting as
well as allowing enough formats for your requirements.
--ron

Nested If statement to control format of number-type cells
 

Creating an event-triggered macro is beyond my current capabilities in Excel.
It's good to know a software upgrade is the other solution, but my office is
behind the curve ball here. If possible, could you provide an example of an
event-triggered macro? Thank you.
--
TB


"Ron Rosenfeld" wrote:

On Wed, 14 May 2008 17:28:00 -0700, TB wrote:

The conditional formatting in Excel doesn't seem to allow for numeric
formatting. I need to control the formatting of numeric cells as shown below-
Please help.

0.00# = 4 decimals
0.0# = 3 decimals
0.## = 2 decimals
1 to 9.99 = 2 decimals
10 to 99.9 = 1 decimal
100 plus = no decimals

You will need to either use a macro, perhaps and event-triggered one; or use
Conditional Formatting in Excel 2007, which does permit number formatting as
well as allowing enough formats for your requirements.
--ron

Nested If statement to control format of number-type cells
 

On Thu, 15 May 2008 14:58:00 -0700, TB wrote:

Creating an event-triggered macro is beyond my current capabilities in Excel.
It's good to know a software upgrade is the other solution, but my office is
behind the curve ball here. If possible, could you provide an example of an
event-triggered macro? Thank you.
--
TB

The following assumes that some of the cells to be formatted contain formulas
(i.e. not just data entry cells).

If ALL cells are data entry cells, the macro could be rewritten to run more
quickly.

To enter this, right click on the worksheet tab; select View Code; and paste
the code below into the window that will open.

Change "r" to be set to the cells you need to have this variable formatting
applied to.

=====================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
'Apply this special formatting to A1:A100
'The bigger the range, the longer this will take
'Macro assumes there may be formulas within range
' If not, could be made more efficient
Set r = Range("A1:A100")
For Each c In r
Select Case c.Value
Case Is < 0.01
c.NumberFormat = "0.0000"
Case Is < 0.1
c.NumberFormat = "0.000"
Case Is < 10
c.NumberFormat = "0.00"
Case Is < 100
c.NumberFormat = "0.0"
Case Else
c.NumberFormat = "#,###"
End Select
Next c
End Sub
===========================
--ron