highlighting cells

[jrussell121]

A B C D E F

1 1 2 3 4 5 6
2 4 5 6 7 8 9
3 1 2 7 8 33 34
4
5
6
7
8
9
10 1 2 7 8 33 50
11 1 2 51 52 53 54






Please could any one help the problem i have is to highlight the background colour of the cells A1:F1,A3:F3 if the number appears in the cells A10:F10,A11:F11 in the example above A1,A3,B1,B3,C3,D2,D3,E2,E3 would be coloured.
That is A10:F10,A11:F11 is being treated as a set and every occurance of a number in the set is then colured in the cells A1:F1,A3:F3
I think that i need a macro to achieve this



regards

john russell

 

[Aladin Akyurek]

On 2002-04-21 13:15, jrussell121 wrote:


A B C D E F

1 1 2 3 4 5 6
2 4 5 6 7 8 9
3 1 2 7 8 33 34
4
5
6
7
8
9
10 1 2 7 8 33 50
11 1 2 51 52 53 54

Please could any one help the problem i have is to highlight the background colour of the cells A1:F1,A3:F3 if the number appears in the cells A10:F10,A11:F11 in the example above A1,A3,B1,B3,C3,D2,D3,E2,E3 would be coloured.
That is A10:F10,A11:F11 is being treated as a set and every occurance of a number in the set is then colured in the cells A1:F1,A3:F3
I think that i need a macro to achieve this

regards

john russell

Select A10:F10, go to the Name Box on the Formula Bar, type Set1, and hit enter.
Select A11:F11, go to the Name Box on the Formula Bar, type Set2, and hit enter.

Select the target range A1:F1.
Activate Format|Conditional Formatting.
Select Formula Is for Condition 1.
Enter as formula:

=OR(ISNUMBER(MATCH(A1,Set1,0),ISNUMBER(MATCH(A1,Set2,0))

Activate Format.
Select a background color on the Patterns tab.
Activate OK, OK.

Select the target range A3:F3.
Activate Format|Conditional Formatting.
Select Formula Is for Condition 1.
Enter as formula:

=OR(ISNUMBER(MATCH(A3,Set1,0),ISNUMBER(MATCH(A3,Set2,0))

Activate Format.
Select a background color on the Patterns tab.
Activate OK, OK.

Aladin
This message was edited by Aladin Akyurek on 2002-04-21 13:31