Conditional formating for currency

C

cfournier

New Member
Joined
May 6, 2008
Messages
19
Ok, i'm working on a table containing prices.
The prices are fetched from another Excel document and placed in several rows/columns on the same page.

At the top of that page are dropdown menus allowing the user to select different items, including the currency (CAD, USD, Euro or RMB). This info is used to fetch the appropriate data (but this is not the problem, works perfectly). I also use that info to format the data appropriatly (to add the $/E/Y character infront/after... the amount).

To do so, i use the conditionnal formating with the formulas =$B$7="USD" (in which the B7 cell contain the currency) and i format the data with the formating options number-> currency -> appropriate format. I create about 6 of these conditions for the different currencies i need, each time changing the text from "USD" to the other ones (obviously!).

The problem is that when you select the currency from the drop down list, the formmating doesn't apply correctly even if the preview in the conditional formating window appears as expected! I tried with the "stop if true" checkbox both checked and uncheck, saved in both .xls and .xlsx still not working :confused:

What am i doing wrong?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Hi

Did you hit 'Apply' before you OK'd out of the CF Wizard?
 
Upvote 0
If you've formatted as currency that's all you've done.

You've not changed the underlying value.

And as far as I know, unless there's something new in 2007, conditional formatting is based on the value not the format.:)
 
Upvote 0
A few questions.
1. What Columns/Rows (be specific) contain currency amounts?
2. Instead of CF, would code be an option?
3. Are you familiar with "Styles" in Excel?
If your ans yes to 2 & 3, then you can use a WorkSheet_Change Event triggered on $B$7 to change the styles of your currency cells. You will 1st have to create a "Custom Style" for each needed currency. A short Example. Say your currencys are in $B$10:$C$10.
Code: 
Private Sub WorkSheet_Change(Byval Target As Range)
If Target.Address <> "$B$7" Then Exit Sub
Select Case Target
    Case "USD": Range("$B$10":$C$10").Style = "USD"
    Case "Euro": Range("$B$10":$C$10").Style = "Euro"
    'etc
    Case Else
End Select
End Sub

If you need to, you can use a similar approach to convert to other currencys.
HTH
lenze
 
Last edited:
Upvote 0
In Excel 2007 you can add rules without applying them

CFXL07.jpg
 
Upvote 0
dave3009 said:
In Excel 2007 you can add rules without applying them

CFXL07.jpg
Click to expand...

This is exactly what i have... except that i apply the formatting to more than one cell.


@ Norie: I don't understand what you mean, maybe you misread my first post?

@ Lenze:
- Yes i'm familiar with styles
- No, code would not be an option as i'm creating a "macro-free" document

@Dave3009: No, i don't thnik it affect the results in Excel 2007 anyway, am I wrong?
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Applying conditional formatting to merged cells
Replies
1
Views
304
Joe4
Joe4
B
  • Question
How to loop columns to find cells formatted as currency, write currency in next col, and reformat
Replies
9
Views
717
Coding4Fun
C
A
  • Question
Reference to a cell and get source currency formatting
Replies
3
Views
1K
GlennUK
G
K
  • Question
Automate pulling Conditional formatting
Replies
1
Views
328
CSmith
CSmith
gisquared
  • Solved
Conditional Format on a column that contains numbers and text in different cells - Need to highlight when it changed from last report
Replies
3
Views
851
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,978
Messages
6,122,547
Members
449,089
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top