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
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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_

Well-known Member
Joined
Jul 12, 2016
Messages
512
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:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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

Board Regular
Joined
Nov 19, 2014
Messages
163
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

Board Regular
Joined
Nov 19, 2014
Messages
163
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
 

Forum statistics

Threads
1,082,358
Messages
5,364,914
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top