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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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