Set number format based on cell contents

[Guest]

I have a cell B3 which shows a Data Validation list of £ or %

Having selected £ or % in B3 the user then enters a number in C3. I want to
have cell C3 formatted to General if B3 = % and formatted to Currency if B3 =
£.

I'd really appreciate some help

Thanks

 

[Otto Moehrbach]

You need VBA for that. Paste the following macro into the sheet module for
the sheet you are working on. To do that, right-click on the sheet tab,
select View Code, and paste this macro into that module.
Note that this macro does exactly what you asked. That is, it will change
the format of C3 if you select $ or % in B3. If you want this to work on
other cells, you will need to alter the macro.
Note that the macro uses $ instead of the British pound symbol. You need to
change that.
Post back if you need more. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "B3" Then
If Target = "$" Then
Target.Offset(, 1).NumberFormat = "$#,##0.00"
Else
Target.Offset(, 1).NumberFormat = "General"
End If
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End Sub

 

[Guest]

Thanks Otto.

I've edited the code as shown below and put it in the Sheet1 Object page but
when I change the cells in B3 nothing happens in C3.

Can't see what I've done wrong but I must have missed something.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "B3" Then
If Target = "£" Then
Target.Offset(, 1).NumberFormat = "£#,##0.00"
Else
Target.Offset(, 1).NumberFormat = "General"
End If
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End Sub

 

[Otto Moehrbach]

All that is going to happen is that the format of C3 will change. If you
mean that the format is not changing then there is a problem. It worked for
me. So let's troubleshoot it.
Put the following line as the first line of the macro:
MsgBox "Working"
If you see the "Working" message box when you change the entry in B3, then
we know the macro is firing.
If you don't see that, then Excel is not recognizing the event of a change
in B3.
Close the file and close Excel.
Open Excel and open the file and see what happens when you change B3.
If this doesn't help, then I have to see the file myself.
If you wish, send me the file direct (do not post the file in the
newsgroup). If your file is large, make a copy of it and cut the file down
to small and make sure that the response of the macro (nothing) is still the
same. My email address is (e-mail address removed). Remove the "nop" from
this address. HTH Otto

 

[Guest]

Thanks a lot Otto.

Tried everything you suggested but it still didn't work so I did the
Ctrl-Alt-Del routine and having restarted Windows and Excel the code works
perfectly. Don't understand why it needed a system restart but I'm just
delighted it works.

Thanks again

 

[Otto Moehrbach]

I would guess that you somehow skipped over the line:
Application.EnableEvents = True
when you were running the macro. This can happen when you incur an error
and the code execution stops. That sets Excel to not recognize any events
and it stays that way until you manually reset Excel, which you did. If
this happens again then we need to put an error trap in the code somewhere.
Post back if this happens again. HTH Otto