hello there,
I am trying to get a cell that has a single number (one to two digits) and would like to format the cell in a fashion similar to this: +N1 (+N1-5) resulting in a format like +10 (+5).
Anyone have any ideas?
AFAIK, you cannot do that with formatting. You cannot do arithmetic operations with formatting directly.
The only way around it would be to generate a custom format in VBA for those cells. But there is a limit of the number of custom formats in a workbook, so if you use the workbook frequently, with different values, you will likely exceed this limit. In Excel 2007 (and I think in 2010 also), that limit is something like 200-250
You could use a formula to display the above, leaving the original untouched:
=TEXT(A1,"\+ 0" & " \(\+" & A1-5 & "\)")
You could use an event triggered macro to custom format the original cells, which would also leave the original value untouched, but you run into that limit of 200-250 number formats
To format the cells in Column A this way:
To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-click drop-down menu.
Then paste the code below into the window that opens.
Be sure to set rg to the range where you want this to occur.
=======================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range, c As Range
Set rg = Cells(1, 1).EntireColumn.SpecialCells(xlCellTypeConstants, xlNumbers)
For Each c In rg
c.NumberFormat = "\+ 0" & " \(\+" & CStr(c.Value - 5) & "\)"
Next c
Set rg = Cells(1, 1).EntireColumn.SpecialCells(xlCellTypeFormulas, xlNumbers)
For Each c In rg
c.NumberFormat = "\+ 0" & " \(\+" & CStr(c.Value - 5) & "\)"
Next c
End Sub
================================
