Conditional Format query

Fazila

Board Regular
Joined
Nov 19, 2014
Messages
163
Is it possible to highlight cells based on another cell. That is a bit of an oversimplification. My table looks something like this:

Choice 1Choice 2Choice 3Choice 4Choice 5Choice 6Option WOption XOption YOption Z
HistorySpanishMediaRSPEArtHistorySpanishArtRS

<tbody>
</tbody>

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:

Choice 1Choice 2Choice 3Choice 4Choice 5Choice 6Option WOption XOption YOption Z
HistorySpanishMediaRSPEArtHistorySpanishArtRS

<tbody>
</tbody>

As always, any help is very much appreciated.

Thanks
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
This would obviously require to separate conditions:

For the first condition (green):
Code:
=G2=OFFSET(G2,0,-6)

For the second condition (yellow):
Code:
=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:
Code:
=OR(G2=$A2,G2=$B2,G2=$C2,G2=$D2)
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top