Conditional formatting for Currency based on cell value

[yorkshirelad]

I need to change the formatting of a range of cells G17:G21 from $dollars to £uk depending on a cell value B2 which shows the currency.

I've tried to set this using Excel 2007 conditional formatting but it only seems to allow you to amend the formatting based on the cells you want to format rather than another cell.

I'm sure I have done this before using a formula, so would be obliged for any assistance

Many thanks

 

[T. Valko]

I need to change the formatting of a range of cells G17:G21 from $dollars to £uk depending on a cell value B2 which shows the currency.

I've tried to set this using Excel 2007 conditional formatting but it only seems to allow you to amend the formatting based on the cells you want to format rather than another cell.

I'm sure I have done this before using a formula, so would be obliged for any assistance

Many thanks

What is in B2? The text entry dollar or pound or what?

Try something like this. We'll assume B2 contain either "US" or UK".

Select the *entire* range G17:G21 starting from cell G17.
Cell G17 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

• Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format
• Enter this formula in the box below:
  =$B$2="us"
• Click the Format button
• Select the Numer tab
• Select Currency
• Select the appropriate currency styles for US
• OK
• OK
• Repeat the process for "UK" but change the formula to: =$B$2="uk"

 

[yorkshirelad]

Thanks - that works a treat, I'd forgotten how to link the formula! Brilliant

 

[T. Valko]

Thanks - that works a treat, I'd forgotten how to link the formula! Brilliant

You're welcome. Thanks for the feedback!

 

[scott marshall]

i am trying to automatically format the currency of a range of cells, depending whether i chose the active currency to be €, $, £, etc i.e. exactly the same but i am using excel 2002.
is it possible pls?
thank you

 

[T. Valko]

i am trying to automatically format the currency of a range of cells, depending whether i chose the active currency to be €, $, £, etc i.e. exactly the same but i am using excel 2002.
is it possible pls?
thank you

Unfortunately that type of conditional formatting is not available in Excel versions prior to Excel 2007.

You would need to use an event macro to do what you want. I'm not much of a programmer so I can't help you with that.

If you haven't recieved a solution after a day or so start a new thread as this one is kind of old.

Good luck!

 

[scott marshall]

thank you for the quick reply.
unfortunately i am not good on macros, so if anyone else can help here, i'd be grateful for the assistance
thank you

 

[ownadi]

Is such formatting still available in current Excel 365? As far as I can see there are only style options like fonts and borders. If no, maybe do you know whether it works in Excel 2016?