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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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,128,154
Messages
5,629,005
Members
416,358
Latest member
grsaltzman

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