On 27 Aug 2006 15:23:57 -0700, "Slashman" wrote:

Hi,

I have a custom number format that prefixes positive numbers and
negative numbers with + and - signs, and a macro line that makes a cell
DP increase by one, based on a key cell DP placement.

IE Key cell DP is: 0.000 and answer cell is therefo 0.0000

Because the number of DP changes for the key cell each time I use the
spreadsheet, I cant
just have it formatted for custom all the time for the + and - prefix,
as when I run my macro to set the DP of that cell, it overrides the
custom formatting for the + or -.


I need both things to happen, is it possible or is there another way to

simultaneously change the DP of the cell and apply the custom number
format for + and - numbers each time?

Cheers,

Aaron.

You could maybe use a macro like this to set both:

=======================================
Option Explicit
Sub IncrDP()
Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String

Set KeyCell = [a3]
Set AnswerCell = [b3]

'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat

DP = InStr(1, kcFmt, ";") - InStr(1, kcFmt, ".")
If InStr(1, kcFmt, ".") = 0 Then DP = 1

'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP)
acFmt = "+" & acFmt & ";-" & acFmt & ";0"

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt

End Sub
======================================

--ron