# Conditional formatting based on another table

#### amp165

##### New Member
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
 2 5 9 4 9 1 4 8 3

<tbody>
</tbody>

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

<tbody>
</tbody>

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
bump

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

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?

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

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

Replies
4
Views
485
Replies
7
Views
162
Replies
4
Views
450
Replies
7
Views
329
Replies
8
Views
368

1,216,483
Messages
6,130,932
Members
449,607
Latest member
babylegs

### 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.

### Which adblocker are you using?

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

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