format cells range
 

Hi
I need to formay a range of cells as per example below:

if A1=USD then B10:B13 format as $
if A1=GBP then B10:B13 Format as £

and so on..
How can I do this automatically as a kind of "conditional formatting"?
Thank you
AD

format cells range
 

Select your range condiditional Formatting
Condition1: =$A$1="GBP"
Format as Number Currency Symbol: select Eng (US)

Condition2: =$A$1="USD"
Format as Number Currency Symbol: select Eng (Uk)


"diacci1st" wrote:

Hi
I need to formay a range of cells as per example below:

if A1=USD then B10:B13 format as $
if A1=GBP then B10:B13 Format as £

and so on..
How can I do this automatically as a kind of "conditional formatting"?
Thank you
AD

format cells range
 

Thank you!
unfortunattly I only get the possibility to format FONT/BORDER/PATTERN in
the conditional formatting.. am I doing something wrong?

Thank you
AD

"Teethless mama" wrote:

Select your range condiditional Formatting
Condition1: =$A$1="GBP"
Format as Number Currency Symbol: select Eng (US)

Condition2: =$A$1="USD"
Format as Number Currency Symbol: select Eng (Uk)


"diacci1st" wrote:

Hi
I need to formay a range of cells as per example below:

if A1=USD then B10:B13 format as $
if A1=GBP then B10:B13 Format as £

and so on..
How can I do this automatically as a kind of "conditional formatting"?
Thank you
AD

format cells range
 

Regular CF won't handle that type of formatting.

Event code could do the trick.

Adjust to suit. DV dropdown for selection assumed A1

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("B10:B13")
Select Case Target.Value
Case "USD"
.NumberFormat = "$#,##0.00"
Case "GBP"
.NumberFormat = "£#,##0.00"
Case "Euro"
.NumberFormat = "€#,##0.00"
End Select
End With
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that module, edit to suit then Alt + q to return to the
Excel window.


Gord Dibben MS Excel MVP

On Tue, 30 Sep 2008 08:31:02 -0700, diacci1st
wrote:

Hi
I need to formay a range of cells as per example below:

if A1=USD then B10:B13 format as $
if A1=GBP then B10:B13 Format as £

and so on..
How can I do this automatically as a kind of "conditional formatting"?
Thank you
AD

format cells range
 

Thanks that is great would it be possible to have the same code as sheet
activate as the selection of the currency is done on a menu page in an other
sheet. but I have just a reference cell on the sheet where I need the change
of formatting
Thank you
AD


"Gord Dibben" wrote:

Regular CF won't handle that type of formatting.

Event code could do the trick.

Adjust to suit. DV dropdown for selection assumed A1

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("B10:B13")
Select Case Target.Value
Case "USD"
.NumberFormat = "$#,##0.00"
Case "GBP"
.NumberFormat = "£#,##0.00"
Case "Euro"
.NumberFormat = "‚¬#,##0.00"
End Select
End With
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that module, edit to suit then Alt + q to return to the
Excel window.


Gord Dibben MS Excel MVP

On Tue, 30 Sep 2008 08:31:02 -0700, diacci1st
wrote:

Hi
I need to formay a range of cells as per example below:

if A1=USD then B10:B13 format as $
if A1=GBP then B10:B13 Format as £

and so on..
How can I do this automatically as a kind of "conditional formatting"?
Thank you
AD

format cells range
 

Assuming A1 of sheet with range to format has a formula like =menusheet!F12

Change the event type to Private Sub Worksheet_Calculate()


Gord

On Tue, 30 Sep 2008 10:16:08 -0700, diacci1st
wrote:

Thanks that is great would it be possible to have the same code as sheet
activate as the selection of the currency is done on a menu page in an other
sheet. but I have just a reference cell on the sheet where I need the change
of formatting
Thank you
AD


"Gord Dibben" wrote:

Regular CF won't handle that type of formatting.

Event code could do the trick.

Adjust to suit. DV dropdown for selection assumed A1

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("B10:B13")
Select Case Target.Value
Case "USD"
.NumberFormat = "$#,##0.00"
Case "GBP"
.NumberFormat = "£#,##0.00"
Case "Euro"
.NumberFormat = "€#,##0.00"
End Select
End With
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that module, edit to suit then Alt + q to return to the
Excel window.


Gord Dibben MS Excel MVP

On Tue, 30 Sep 2008 08:31:02 -0700, diacci1st
wrote:

Hi
I need to formay a range of cells as per example below:

if A1=USD then B10:B13 format as $
if A1=GBP then B10:B13 Format as £

and so on..
How can I do this automatically as a kind of "conditional formatting"?
Thank you
AD

format cells range
 

Hi
I did is the code that I have inserted but I get an error message "procedure
decleration does not meet the description"
Private Sub Worksheet_Calculate(ByVal Target As Range)
If Intersect(Target, Me.Range("J2")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("F15:H20")
Select Case Target.Value
Case "USD"
.NumberFormat = "$#,##0.00"
Case "GBP"
.NumberFormat = "£#,##0.00"
Case "KRN"
.NumberFormat = "Kr#,##0.00"
End Select
End With
endit:
Application.EnableEvents = True
End Sub


"Gord Dibben" wrote:

Assuming A1 of sheet with range to format has a formula like =menusheet!F12

Change the event type to Private Sub Worksheet_Calculate()


Gord

On Tue, 30 Sep 2008 10:16:08 -0700, diacci1st
wrote:

Thanks that is great would it be possible to have the same code as sheet
activate as the selection of the currency is done on a menu page in an other
sheet. but I have just a reference cell on the sheet where I need the change
of formatting
Thank you
AD


"Gord Dibben" wrote:

Regular CF won't handle that type of formatting.

Event code could do the trick.

Adjust to suit. DV dropdown for selection assumed A1

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("B10:B13")
Select Case Target.Value
Case "USD"
.NumberFormat = "$#,##0.00"
Case "GBP"
.NumberFormat = "£#,##0.00"
Case "Euro"
.NumberFormat = "‚¬#,##0.00"
End Select
End With
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that module, edit to suit then Alt + q to return to the
Excel window.


Gord Dibben MS Excel MVP

On Tue, 30 Sep 2008 08:31:02 -0700, diacci1st
wrote:

Hi
I need to formay a range of cells as per example below:

if A1=USD then B10:B13 format as $
if A1=GBP then B10:B13 Format as £

and so on..
How can I do this automatically as a kind of "conditional formatting"?
Thank you
AD

format cells range
 

Private Sub Worksheet_Calculate() 'only

drop this bit........ByVal Target As Range


Gord

On Tue, 30 Sep 2008 12:22:14 -0700, diacci1st
wrote:

Private Sub Worksheet_Calculate(ByVal Target As Range)
If Intersect(Target, Me.Range("J2")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("F15:H20")
Select Case Target.Value
Case "USD"
.NumberFormat = "$#,##0.00"
Case "GBP"
.NumberFormat = "£#,##0.00"
Case "KRN"
.NumberFormat = "Kr#,##0.00"
End Select
End With
endit:
Application.EnableEvents = True
End Sub

format cells range
 

Hi
If I do that I get an error on the the below line for "object required"
If Intersect(Target, Me.Range("J2")) Is Nothing Then Exit Sub



"Gord Dibben" wrote:

Private Sub Worksheet_Calculate() 'only

drop this bit........ByVal Target As Range


Gord

On Tue, 30 Sep 2008 12:22:14 -0700, diacci1st
wrote:

Private Sub Worksheet_Calculate(ByVal Target As Range)
If Intersect(Target, Me.Range("J2")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("F15:H20")
Select Case Target.Value
Case "USD"
.NumberFormat = "$#,##0.00"
Case "GBP"
.NumberFormat = "£#,##0.00"
Case "KRN"
.NumberFormat = "Kr#,##0.00"
End Select
End With
endit:
Application.EnableEvents = True
End Sub

format cells range
 

Yes......you would<g My slip.

Try this version which assumes J2 has a linking formula to a changing cell
on another sheet.

I also added the UCase to make the value in J2 case-insensitive.

i.e. usd or uSd or USd would be OK

Private Sub Worksheet_Calculate()
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("F15:H20")
Select Case UCase(Me.Range("J2").Value)
Case "USD"
.NumberFormat = "$#,##0.00"
Case "GBP"
.NumberFormat = "£#,##0.00"
Case "KRN"
.NumberFormat = "Kr#,##0.00"
End Select
End With
endit:
Application.EnableEvents = True
End Sub

Gord

On Tue, 30 Sep 2008 13:50:01 -0700, diacci1st
wrote:

Hi
If I do that I get an error on the the below line for "object required"
If Intersect(Target, Me.Range("J2")) Is Nothing Then Exit Sub



"Gord Dibben" wrote:

Private Sub Worksheet_Calculate() 'only

drop this bit........ByVal Target As Range


Gord

On Tue, 30 Sep 2008 12:22:14 -0700, diacci1st
wrote:

Private Sub Worksheet_Calculate(ByVal Target As Range)
If Intersect(Target, Me.Range("J2")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("F15:H20")
Select Case Target.Value
Case "USD"
.NumberFormat = "$#,##0.00"
Case "GBP"
.NumberFormat = "£#,##0.00"
Case "KRN"
.NumberFormat = "Kr#,##0.00"
End Select
End With
endit:
Application.EnableEvents = True
End Sub