Formatting

[Barry]

How do I prevent the number formatting I have applied to a
range of cells being overwritten?

I have used accountancy format but it is possible to
overwrite by putting in the currency symbol followed by
the value.
I've looked at Protection, Conditional Formatting but
can't find the solution.

 

[JE McGimpsey]

You could use an event macro. Say the range of cells you want in
accounting format is A1:J1 and B10. One way:

Put this in the worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Set rTarget = Intersect(Target, Range("A1:J1,B10"))
If Not rTarget Is Nothing Then _
rTarget.NumberFormat = _
"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End Sub

Note that this will not prevent reformatting the cells after entry of a
value. If you want to ensure that the cells stay formatted, you could
use the selection change event (it won't prevent reformatting, but this
will change the format back whenever a new cell is selected):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Range("A1:J1,B10").NumberFormat = _
"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End Sub

 

[Barry]

Thanks very much, I used the second method.

-----Original Message-----
You could use an event macro. Say the range of cells you want in
accounting format is A1:J1 and B10. One way:

Put this in the worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Set rTarget = Intersect(Target, Range ("A1:J1,B10"))
If Not rTarget Is Nothing Then _
rTarget.NumberFormat = _
"_($* #,##0.00_);_($* (#,##0.00);_($* ""- ""??_);_(@_)"
End Sub

Note that this will not prevent reformatting the cells after entry of a
value. If you want to ensure that the cells stay formatted, you could
use the selection change event (it won't prevent reformatting, but this
will change the format back whenever a new cell is selected):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Range("A1:J1,B10").NumberFormat = _
"_($* #,##0.00_);_($* (#,##0.00);_($* ""- ""??_);_(@_)"
End Sub



.