I want to change the format of the number in the cell based on a condition.
It seems that this function is limited to font, border and patterns.
Any suggestion?

Indeed it is. You could try this addin,
http://www.xldynamic.com/source/xld.....Download.html

or code like

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

Select Case .Value

Case "Currency": .Offset(0, 1).NumberFormat = "£#,##0.00"
Case "Number": .Offset(0, 1).NumberFormat = "#,##0.00"
Case "Percent": .Offset(0, 1).NumberFormat = "0%"
'wtc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Almir Vargas" wrote in message
...
I want to change the format of the number in the cell based on a condition.
It seems that this function is limited to font, border and patterns.
Any suggestion?

Bob, thanks so much for the promptly help.
The problem is that I don't know how to use the codes, so it is difficult
for me to adapt to what I need.
So, to be more specific, I would like to have the range O7:Q29 changing to
number format 0.00% if P5 = to "Variance to Prior %" and changing number
format to 0.00 if P5 = "Variance to Prior $".

Could you help me adapting the code or finding another solution w/o using
code or addins?
thank you


"Bob Phillips" wrote:

Indeed it is. You could try this addin,
http://www.xldynamic.com/source/xld.....Download.html

or code like

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

Select Case .Value

Case "Currency": .Offset(0, 1).NumberFormat = "£#,##0.00"
Case "Number": .Offset(0, 1).NumberFormat = "#,##0.00"
Case "Percent": .Offset(0, 1).NumberFormat = "0%"
'wtc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Almir Vargas" wrote in message
...
I want to change the format of the number in the cell based on a condition.
It seems that this function is limited to font, border and patterns.
Any suggestion?

I think this will do what you want

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "P5" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

Select Case .Value

Case "Variance to Prior %":
Me.Range("Q7:Q29").NumberFormat = "0.00%"
Case "Variance to Prior $":
Me.Range("Q7:Q29").NumberFormat = "#,##0.00"
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Almir Vargas" wrote in message
...
Bob, thanks so much for the promptly help.
The problem is that I don't know how to use the codes, so it is difficult
for me to adapt to what I need.
So, to be more specific, I would like to have the range O7:Q29 changing to
number format 0.00% if P5 = to "Variance to Prior %" and changing number
format to 0.00 if P5 = "Variance to Prior $".

Could you help me adapting the code or finding another solution w/o using
code or addins?
thank you


"Bob Phillips" wrote:

Indeed it is. You could try this addin,
http://www.xldynamic.com/source/xld.....Download.html

or code like

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

Select Case .Value

Case "Currency": .Offset(0, 1).NumberFormat =
"£#,##0.00"
Case "Number": .Offset(0, 1).NumberFormat =
"#,##0.00"
Case "Percent": .Offset(0, 1).NumberFormat = "0%"
'wtc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Almir Vargas" wrote in message
...
I want to change the format of the number in the cell based on a
condition.
It seems that this function is limited to font, border and patterns.
Any suggestion?