Conditional Format - ????

[August]

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

 

[rs2k]

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]

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]

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]

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]

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