Number Format based on Cell Content

[AirgasRob]

Thanks in advance, been looking at other post and couldn't quite get it to
work.

So what I need is such;

Cell A1 = "Number" then cells A2 through A14 are formatted as numbers with 2
decimal places. Otherwise they are numbers with no decimal places. I also
need both number formats to have the "," seperator.

I also keep the worksheet locked with some of the cells unlocked. In order
for this macro to work would I need to have the entire sheet unlocked, just
the cells, or neither.

 

[Ken]

Rob

Assuming the sheet is protected, wtih password "test" and cell A1 not
protected, if you put the following code in the worksheet_change
event, it will do what I think you want.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then

ActiveSheet.Unprotect Password:="test"

If Target.Value = "Number" Then
Range("A2:A14").NumberFormat = "#,##0.00"
Else
Range("A2:A14").NumberFormat = "#,##0"
End If

ActiveSheet.Protect Password:="test"

End If

End Sub

If cell A1 is changed programmatically or if it is a formula, that
sometimes returns "Number" then you will have to trigger the macro
some other way. In this event code, it runs everytime the worksheet
changes, but, if the cell that triggers the event is not A1, it gets
out right away. If it is A1 that has been changed, it formats cells
A2:A14 appropriately.

If you want the macro to run less often, or more often, you can use
the same basic code in a regular module, attached to a button, or a
shortcut, or stick it in another event code. If you do any of those
you just need to get rid of the outer (if target=) loop.

Good luck.

Ken
Norfolk, Va

 

[AirgasRob]

Thank you Ken, works perfect.

Rob

Assuming the sheet is protected, wtih password "test" and cell A1 not
protected, if you put the following code in the worksheet_change
event, it will do what I think you want.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then

ActiveSheet.Unprotect Password:="test"

If Target.Value = "Number" Then
Range("A2:A14").NumberFormat = "#,##0.00"
Else
Range("A2:A14").NumberFormat = "#,##0"
End If

ActiveSheet.Protect Password:="test"

End If

End Sub

If cell A1 is changed programmatically or if it is a formula, that
sometimes returns "Number" then you will have to trigger the macro
some other way. In this event code, it runs everytime the worksheet
changes, but, if the cell that triggers the event is not A1, it gets
out right away. If it is A1 that has been changed, it formats cells
A2:A14 appropriately.

If you want the macro to run less often, or more often, you can use
the same basic code in a regular module, attached to a button, or a
shortcut, or stick it in another event code. If you do any of those
you just need to get rid of the outer (if target=) loop.

Good luck.

Ken
Norfolk, Va