Re: Conditional Formatting

Hello

I am trying to highlight in yellow specific cells on a worksheet when I pull up a word from a list,
am having trouble in accomplishing the following task:

In cell B6 I have a list of 14 world markets (countries) we trade in.
When one of these is selected I would like to highlight the following cells that respectfully match that market.

M O R S T U

2 Hong Kong 9:58 AM HKD / CAD 6.349 HKD / USD 7.7984
3 Singapore 9:58 AM SGD / CAD 1.3274 SGD / USD 1.6305
4 Japan 10:58 AM JPY / CAD 83.476 JPY / USD 102.53
5 Australia 12:58 PM CAD/ AUD 0.9337 " " " "
6 New Zealand 2:58 PM " " " " " " " "
7 Thailand 8:58 AM " " " " " " " "
8 South Africa 3:58 AM " " " "
9 Switzerland 2:58 AM
10 Sweden 2:58 AM
11 Denmark 2:58 AM
12 Norway 2:58 AM
13 U. K. 1:58 AM
14 Finland 3:58 AM
15 E. U. 2:58 AM

For example when Japan is selected in cell B6 it has the effect of highlighting in yellow cells: M4, O4, R4:U4

Thank you

Susan

You may not need a programming solution if you use a DropDownList for Cell B6:
Data>Validation
Allow: List
Source: Select the list of Country Names
Click [OK].

Then you could just use Conditional Formatting:

1)Insert a column just before the Country Names.

We'll start with Hong Kong, which was entered in Cell M2, but is now in Cell
N2.

2)In Cell M2, type this formula:

=$B$6=N2
Note: if Cell B6 contains "Hong Kong", the formula will return TRUE.

3)Now copy that formula down the cells in front of each Country Name.

4)Select the list of Country Names (and any other cells you want colored).

5)Format>Conditional Formatting.

6)Click the drop-down list and select "Formula Is".

7)Enter this in the Formula Box:
=$M2=TRUE

8)Click the [Format] button and select the fill color you want for the cells.

9)Click the OK button and you're done!

Now, the whenever the country named in Cell B6 changes, the associated cells
in your list will change color and the previously colored cells will revert
back to their default.

Does that help?

Regards,
Ron