Set number format based on cell contents
 

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

Set number format based on cell contents
 

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
"nospaminlich" wrote in message
...
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

Set number format based on cell contents
 

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

Set number format based on cell contents
 

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 . Remove the "nop" from
this address. HTH Otto
"nospaminlich" wrote in message
...
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

Set number format based on cell contents
 

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

Set number format based on cell contents
 

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
"nospaminlich" wrote in message
...
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