Conditional Formatting Based on List

[LeonardH]

Hello!

Simply, I was wondering if anyone had a method for conditional formatting (Font Color) for a row based on whether or not the last four digits of an asset appear in a separate table.

(Table A) I've got a spreadsheet that has a large list of equipment as well as columns with current equipment hours (plausible hours shown) before the next time the equipment is to be serviced. The equipment asset designation is in the first column.

(Table B) I've also got a small table to show which equipment is planned to be serviced in which week.

How would I go about conditionally formatting the hours in each equipment row (columns B through J) in Table A based on the week column in Table B in which the last 4 digits of the asset appears?

Table A:

Asset Number
CZ1-1123-RH2200 <tbody> </tbody>				242
CZ1-1123-RH2201 <tbody> </tbody>		100
CZ1-1123-RH2202 <tbody> </tbody>			150
CZ1-1123-RH2203 <tbody> </tbody>	53
CZ1-1123-RH2204 <tbody> </tbody>					298
CZ1-1124-RC2260 <tbody> </tbody>							400
CZ1-1123-RH2281 <tbody> </tbody>						380
CZ1-1124-RW5515 <tbody> </tbody>								453
CZ1-1124-RW5516 <tbody> </tbody>									520

<tbody>
</tbody>

Table B:

Week 1	Week 2	Week 3
2203	2201	2260
2202		5515
		5516

<tbody>
</tbody>

Thanks in advance!!!

 

[DanteAmor]

You need 3 formulas, one for each color.
Eg.

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:113px;" /><col style="width:57px;" /><col style="width:57px;" /><col style="width:57px;" /><col style="width:57px;" /><col style="width:57px;" /><col style="width:57px;" /><col style="width:57px;" /><col style="width:57px;" /><col style="width:57px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Asset Number</td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >CZ1-1123-RH2200</td><td > </td><td > </td><td > </td><td style="text-align:right; ">242</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >CZ1-1123-RH2201</td><td > </td><td style="text-align:right; ">100</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >CZ1-1123-RH2203</td><td style="text-align:right; ">53</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >CZ1-1123-RH5515</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">453</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="color:#00ff00; text-align:center; ">Week 1</td><td style="color:#00b0f0; text-align:center; ">Week 2</td><td style="color:#7030a0; text-align:center; ">Week 3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">2203</td><td style="text-align:right; ">2201</td><td style="text-align:right; ">2260</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">2202</td><td > </td><td style="text-align:right; ">5515</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">5516</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">2200</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table> <br /><br />


-------

<b></b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formulas conditional formatting</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td ></td><td >Formula</td></tr><tr><td ></td><td >=MATCH(VALUE(RIGHT($A2,4)),$A$16:$A$20,0)</td></tr><tr><td ></td><td >=MATCH(VALUE(RIGHT($A2,4)),$B$16:$B$20,0)</td></tr><tr><td ></td><td >=MATCH(VALUE(RIGHT($A2,4)),$C$16:$C$20,0)</td></tr></table></td></tr></table> <br /><br />




Applies to:
=$B$2:$J$10
=$B$2:$J$10
=$B$2:$J$10

 

[LeonardH]

That was perfect! Thanks. That helps so much

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.