Conditional Format - ????

August

Active Member
Joined
Jun 18, 2004
Messages
264
Hi there

I have a cell that is data validated to only accept the input "US Dollar" or "Sterling" I would like the formatting of that cell to change depending on the currency selected. I've had a look at conditional formatting and some of the posts on the board but I can't get a solution that works for me.

Thanks
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
How about creating two buttons (forms toolbar) and labeling one "Sterling" and the other "USD". Then record a macro for each, that way you can switch between your chosen currency, I just tried it and it works fine.
If you need any help post back.

Colin
 

August

Active Member
Joined
Jun 18, 2004
Messages
264
I'll try that, but I'd like something a lttle simpler. The reason being that the spreadsheet is a form that others will be sent to fill in, I'd like them to choose a currency form the drop down list and have the amount cell format automatically. I don't think getting people to find buttons on the toolbar is going to work. By the way would the button "travel" with the spread sheet.

Thanks
 

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
Sorry, can't help you. my VB isn't that good. Option buttons could be used instead of buttons (but, I know that doesn't help you) as for the travelling, PASS, perhaps one of the gurus could help.

Colin.
 

carlc

New Member
Joined
Nov 17, 2002
Messages
1
August,

If you want your validation cell ("US Dollar" or "Sterling") to be conditionally formatted, just select those cells and use the Conditional Format option from the Format menu. Choose "Cell value is" and then type in either ="US Dollar" or ="Sterling" and then choose the desired format. Next, click the Add button to add a 2nd condition and do the same for the opposite value and choose a different formatting.

If you want the actual amount cell to be formatted depending on the selection of the validation-set cells then:

Select the cells to be conditionally formatted and again use the Conditional Format option from the Format menu. In this case choose "Formula Is" and then type this formula in the field: =B2="US Dollar" (this assumes B2 is the starting validated cell) and then choose the desired formatting. Next click Add and add a 2nd condition for the cell to equal Sterling as follows: =B2="Sterling" and choose the desired formatting.

Carl
 

August

Active Member
Joined
Jun 18, 2004
Messages
264
Sorry for not getting back, I didn't realise anyone had posted.........anyway

The formatting I want in the validated cell is £* #,##0.00:[Red]£* (#,##0.00) when Sterling is selected and $* #,##0.00:[Red]$* (#,##0.00) when US Dollars are selected, can your solution be amended to solve this

Many thanks
 

Forum statistics

Threads
1,148,229
Messages
5,745,483
Members
423,953
Latest member
MrC54

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
Top