Cell Color changed based on 2 values

[Jesavoy00]

So i have 2 Tables

Table A, Is a Sign out/In table for computers. This table has 10 columns, from name of computer, serial, whats been signed out, dates, whos signed out and from who, returned date and a notes section.
The notes section i want to be the cell color to be changed by values from Table 2s Status Cell and be corresponding serial number.

Table 2 is the maintenence table, if anything is broken or needs ti be sent to shop or is ready to be picked up the status column, and the same info as table 1.

So i want the serial from table 2 to be matched with serial in table 1, and the color of the notes section or based on the words in table 2 Notes, i want it to change the corresponding lines note sections cell in Table 1.

Yes there's much better ways ti set up info but i dumbed it down A lonnnnnng way for some people.

 

[etaf]

what colours are you using based on the status column of table 2

you could do a countifs() as a conditional format formula

=countifs(column in table 2 with serno, table 1 cell , column in table 2 with status, status value) >0

probably a little more info on the table layouts

Mockup

Book1
	A	B	C	D	E	F	G
1	TABLE1 - Serno	Notes				Table 2 serno	Status
2	1					4	Broken
3	2					8	Broken
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Expression	=COUNTIFS($F:$F,$A1,$G:$G,"broken")>0	text	NO

 

[Jesavoy00]

im using several colors for, Completed is green, Ticket Needed orange, Need to be picked up is lime, replaced is light blue, Not Done is Red. So whatever status is here, i need the serial number that is in this same row to look for the row in table 1 with the same serial number and change the color of the notes cell to the same color as the status cell in table 2.

 

[etaf]

just setup a rule for each colour
Rule1
=COUNTIFS($F:$F,$A1,$G:$G,"completed")>0
Green

Rule2
=COUNTIFS($F:$F,$A1,$G:$G,"ticket needed")>0
orange

ETC

Book1
	A	B	C	D	E	F	G
1	TABLE1 - Serno	Notes				Table 2 serno	Status
2	1					4	Broken
3	2					8	Broken
4	3					2	ticket needed
5	4					9	completed
6	5					4	ticket needed
7	6					3	completed
8	7
9	8
10	9
11	10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Expression	=COUNTIFS($F:$F,$A1,$G:$G,"completed")>0	text	NO
B:B	Expression	=COUNTIFS($F:$F,$A1,$G:$G,"ticket needed")>0	text	NO