Excel 2010 Conditional Formatting question

[Cyclonesrule91]

I am using Excel 2010. I am building a spreadsheet the covers a couple different types of insurance. Two types of insurance insure pounds of product, 3 types of insurance insure Revenue on the product based on pounds and price. You select the type of policy and I have the formulas in place to do the calculations. I then have a sub total colum where the total Insurance guarantee is calculated. What I want to do is have it formatted so in case I select one of the two types of insurance that insure pounds that the format would be a number, and if I select one of the types that insure revenue that the format would be in dollars.

Is there a way to do this?

Thank you in advance.

 

[CyRyder]

Not sure you can use conditional formatting in that way (to format cell contents currency vs number) based another cell input. I know you can make a cell certain colors and to say certain things based on the input of another cell, however.

You could run Macros if you have a Basic ****** that would hide certain rows or columns based on a cell attribute. So if you select "Pounds", the rows that are hidden are the ones with the cells formatted in Currency, and if you select "Revenue", the rows that are hidden are the cells formatted in Numbers. Not sure if you want to go to that extent, but that's how you can format cells as you are describing above.

 

[CyRyder]

$cript is what got censored in my reply, btw.

 

[azepp]

I'm using 2007 and there is a way to do it. Here are the steps in 2007 - hopefully they are similar in 2010.

Select the cell you want the conditional formatting to apply to. On the Home ribbon clink on Conditional Formatting and select New Rule. A window opens that gives me an option of what kind of rule I want. On of the choices is "Use a formula to determine which cells to format". Select that.

Then I enter my formula where it says "Format values where this formula is true:". If cell A1 is the has the text "lb" for a policy that is denomimated in pounds, type =$A$1="lb"

Then click the "Format..." button and select the formatting you want to apply in that case.

 

[Naughtius]

$cript is what got censored in my reply, btw.

Potty mouth:smile:

 

[Cyclonesrule91]

Not sure you can use conditional formatting in that way (to format cell contents currency vs number) based another cell input. I know you can make a cell certain colors and to say certain things based on the input of another cell, however.

You could run Macros if you have a Basic ****** that would hide certain rows or columns based on a cell attribute. So if you select "Pounds", the rows that are hidden are the ones with the cells formatted in Currency, and if you select "Revenue", the rows that are hidden are the cells formatted in Numbers. Not sure if you want to go to that extent, but that's how you can format cells as you are describing above.

Thanks for the reply. I think with this explanation you helped me figure out how to conditionally format it. When you brought up the dissapearing columns based on cell attribute, I went to conditional formatting and formatted based on a formula. Then I made a cell that would either have Pounds or Revenue in it based on which type of insurance. So the formulay I put in was if that cell equals pounds, then number format, otherwise it is revenue based.

So thanks a lot for the help.....

Edit: Thanks a lot Azepp. You helped me out as well.

 

[azepp]

Create a new rule for revenue where cell A1 maybe says "rev" instead of "lb". The steps are the same except that your formula is now =$A$1="rev" and your chosen formatting would be different.

Edit: Never mind! Looks like you got it.