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