Conditional Format query

[Fazila]

Is it possible to highlight cells based on another cell. That is a bit of an oversimplification. My table looks something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Choice 1[/TD]
[TD]Choice 2[/TD]
[TD]Choice 3[/TD]
[TD]Choice 4[/TD]
[TD]Choice 5[/TD]
[TD]Choice 6[/TD]
[TD]Option W[/TD]
[TD]Option X[/TD]
[TD]Option Y[/TD]
[TD]Option Z[/TD]
[/TR]
[TR]
[TD]History[/TD]
[TD]Spanish[/TD]
[TD]Media[/TD]
[TD]RS[/TD]
[TD]PE[/TD]
[TD]Art[/TD]
[TD]History[/TD]
[TD]Spanish[/TD]
[TD]Art[/TD]
[TD]RS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If the entries under Options w to Z match choice 1 to 4 I want to highlight in green, if options W to Z match entries under Choice 5 and 6 I want to highlight in yellow. Is there any way this can be done. So the table should look like:

[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD]Choice 1[/TD]
[TD]Choice 2[/TD]
[TD]Choice 3[/TD]
[TD]Choice 4[/TD]
[TD]Choice 5[/TD]
[TD]Choice 6[/TD]
[TD]Option W[/TD]
[TD]Option X[/TD]
[TD]Option Y[/TD]
[TD]Option Z[/TD]
[/TR]
[TR]
[TD]History[/TD]
[TD]Spanish[/TD]
[TD]Media[/TD]
[TD]RS[/TD]
[TD]PE[/TD]
[TD]Art[/TD]
[TD]History[/TD]
[TD]Spanish[/TD]
[TD]Art[/TD]
[TD]RS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

As always, any help is very much appreciated.

Thanks

 

[Special-K99]

Your example data

How many rows does this cover? Looks like 3 rows with numbers and letters on the row 2 but cant tell cos they're not in boxes/cells.

 

[Tim_Excel_]

This would obviously require to separate conditions:

For the first condition (green):

=G2=OFFSET(G2,0,-6)

For the second condition (yellow):

=OR(G2=$E2,G2=$F2)

Tested on the data you provided (assuming you have headers)

I assumed that the first condition would only be true if the first option matches with the first choice, the second with the second.. etc. If this is not the case, you can resort to the second condition I listed:

=OR(G2=$A2,G2=$B2,G2=$C2,G2=$D2)

 

[Special-K99]

I'm assuming the cells to highlight are on row 2

Select the range of cells to highlight, e.g. G2:J2

Conditional Formatting
New Rule
Use a formula to determine...

=MATCH(G2,$A2:$F2,0)<=4
format as green font

=MATCH(G2,$A2:$F2,0)>=5
format as yellow font

 

[Fazila]

Your example data

How many rows does this cover? Looks like 3 rows with numbers and letters on the row 2 but cant tell cos they're not in boxes/cells.

Sorry should have added the grid lines there were 2 rows the header row and a sample row of data. Tim_Excel_'s solution worked great but thank you for taking the time to post.

 

[Fazila]

I'm assuming the cells to highlight are on row 2

Select the range of cells to highlight, e.g. G2:J2

Conditional Formatting
New Rule
Use a formula to determine...

=MATCH(G2,$A2:$F2,0)<=4
format as green font

=MATCH(G2,$A2:$F2,0)>=5
format as yellow font

That works as well thanks Special-K99

 

[Fazila]

And thanks to you as well Tim_Excel_

 

[Tim_Excel_]

Glad we could help