Conditional formating for currency

[cfournier]

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

What am i doing wrong?

 

[dave3009]

Hi

Did you hit 'Apply' before you OK'd out of the CF Wizard?

 

[Norie]

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.

 

[lenze]

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.

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

 

[dave3009]

In Excel 2007 you can add rules without applying them

 

[cfournier]

In Excel 2007 you can add rules without applying them

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?