Conditional formatting based on another table

amp165

New Member
Joined
Aug 25, 2014
Messages
6
Hi, I'd like to format cells dynamically with background colors based on the numbers in the cell. Below is a basic example of what I am working with (mine is much more complex). So I would want all the "fast" numbers from sheet 2 (A1:A3) to be green, "medium" (A4:A6) yellow, and "slow" (A7:A10) to be red. I know how to conditionally format, but I'm wondering if there's a formula I can set for CF Format only cells that contain... cell value equal to _________ that can dynamically change the background colors based on my second table.

sheet 1
259
491
483

<tbody>
</tbody>

sheet 2
1fast
3fast
8fast
2medium
4medium
9medium
7slow
5slow
6slow
10slow

<tbody>
</tbody>


Thanks for your help
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Using "rules" or Formula Is, depending on your version

=OR(A1={1,3,8}) for green

=OR(A1={2,4,9}) for yellow

=OR(A1={5,6,7,10}) for red
 

amp165

New Member
Joined
Aug 25, 2014
Messages
6
Thanks for the help. My set is much, much larger though and it would take forever to type each individual case for each condition. Is there any way you know of to just color it if the number is found within another table?
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Not sure I understand your question, but......

=COUNTIF(FastRange,A1)>=1

Where Fast Range houses the criterias for Fast.

substitute that range for the other criterias
 

amp165

New Member
Joined
Aug 25, 2014
Messages
6
I figured it out. Your method wasn't working for me, probably a communication error on my part, but for reference:


With whole sheet 1 table selected: New Formatting Rule --> Use a formula to determine which cells to format

3 Rules:

=MATCH(A2,Sheet2!$A$1:$A$3,0) for green
=MATCH(A2,Sheet2!$A$4:$A$6,0) for yellow
=MATCH(A2,Sheet2!$A$7:$A$10,0) for red
 

Watch MrExcel Video

Forum statistics

Threads
1,127,898
Messages
5,627,526
Members
416,250
Latest member
darius_rebelo

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
Top