Formatting cell depending on drop down box value

[Andrela]

Hi,

I have a worksheet where one of the columns uses a drop down box with 5 values. I would like that when I choose a certain value in the box, the next cell to the right is formatted in a predefined way. I tried to use Conditional Formatting, but apparently my wish is to complex. I guess I need a macro, could somebody please help me with that?

Thank you,
Andrela

 

[AnAnalyst]

Hi Andrela,

one of the columns uses a drop down box with 5 values

... I assume you're using data validation? What are the five values?

next cell to the right is formatted in a predefined way

... what are the rules you want for formatting the cell?

 

[Andrela]

The values and formattings are:

• MS - Currency $ English (U.S.) 1 decimal
• TS - Currency $ English (U.S.) 1 decimal
• Pr - Percentage 2 decimals
• Dates - dd/mm/yyyy
• Other - Number, 2 decimals, 1000 Separator (,)

I sort of managed it with Conditional Formatting, using the "use a formula to determine which...", but I have to rewrite the rule for each cell, because the dragging won't change the reference cell ( $G$13="Pr" won't become $G$14="Pr" when dragged down).
I'm using Excel 2007.

Thank you for your interest.

 

[AnAnalyst]

$G$13="Pr" won't become $G$14="Pr" when dragged down

... in the formula box in conditional formatting change the text to $G13="Pr" (i.e. remove the second dollar sign) and then try dragging down.

Hope this helps and all the best!