Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format control on large number of checkboxes | Excel Worksheet Functions | |||
Format Control on large number of check boxes | Excel Worksheet Functions | |||
format control for large number of check boxes | Excel Worksheet Functions | |||
Excel Format Cells - Number Tab - Custom - Order of Type Window. | Excel Discussion (Misc queries) | |||
Adding new 'Type' to Format->Number->Time->Type | Excel Discussion (Misc queries) |