How to change cell colors when another cell is affected

[samk379]

I'm working on setting up a work order tracking system for a small shop. I was able to get to the point where an order is scanned and then the location is scanned and that data is inputted into a table (the two-column table on the right side of the sheet). I also have another table set up that is a better visual measure of progress. If order RD0001 is at the DRILL station, for example, the cell at the intersection of the RD0001 row and the DRILL column fills green. Right now, when the order moves along to the next step, it blanks the previous cell and fills in the cell of the new location. Is there a way I can make it so that the previous cell fills in black so that we could see which orders required which steps? I am also wondering if there is a way that when an order is scanned as complete, the date and/or time of that scan shows in the COMPLETE column, but this is less important to the system function.

Any help would be greatly appreciated, thank you!

Work Order Status Sheet.XLSX
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Barcode Reader	Start in B3
2
3	Order
4	Location
5
6
7		Order Number	Date	Due Date	CNCPLASMA	DEBURR	PRESSBRAKE	SAW	LATHE1	LATHE2	MACHINING	DRILL	TACK	WELD1	WELD2	GRIND	PAINT	ASSEMBLY	COMPLETE		Order	Location
8		TFD0012																			TFD0012	CNCPLASMA
9		CHD0036																			CHD0036	DEBURR
10		GD0009																			GD0009	SAW
11		LE0012																			LE0012	PRESSBRAKE
12		RR0001																			RR0001	LATHE2
13		RD0001																			RD0001	DRILL
14		HT0001																			HT0001	MACHINING
15
16
17
18
19
20
21
22
23
Reader

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R8:R1613	Expression	=V8="ASSEMBLY"	text	NO
Q8:Q1613	Expression	=V8="PAINT"	text	NO
S8:S1613	Expression	=V8="COMPLETE"	text	NO
P8:P1613	Expression	=V8="GRIND"	text	NO
O8:O1613	Expression	=V8="WELD2"	text	NO
N8:N1613	Expression	=V8="WELD1"	text	NO
M8:M1613	Expression	=V8="TACK"	text	NO
L8:L1613	Expression	=V8="DRILL"	text	NO
K8:K1613	Expression	=V8="MACHINING"	text	NO
J8:J1613	Expression	=V8="LATHE2"	text	NO
I8:I1613	Expression	=V8="LATHE1"	text	NO
H8:H1613	Expression	=V8="SAW"	text	NO
G8:G1613	Expression	=V8="PRESSBRAKE"	text	NO
F8:F1613	Expression	=V8="DEBURR"	text	NO
E8:E1613	Expression	=V8="CNCPLASMA"	text	NO

 

[DanteAmor]

Hi and welcome to MrExcel.

Do you need this?

varios 08nov2024.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Barcode Reader	Start in B3
2
3	Order
4	Location
5
6
7		Order Number	Date	Due Date	CNCPLASMA	DEBURR	PRESSBRAKE	SAW	LATHE1	LATHE2	MACHINING	DRILL	TACK	WELD1	WELD2	GRIND	PAINT	ASSEMBLY	COMPLETE		Order	Location
8		TFD0012																			TFD0012	CNCPLASMA
9		CHD0036																			CHD0036	DEBURR
10		GD0009																			GD0009	SAW
11		LE0012																			LE0012	PRESSBRAKE
12		RR0001																			RR0001	LATHE2
13		RD0001																			RD0001	DRILL
14		HT0001																			HT0001	MACHINING
15
16
Hoja6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E8:S1000	Expression	=COLUMN()<MATCH($V8,$A$7:$S$7,0)	text	NO
E8:S1000	Expression	=VLOOKUP($B8,$U:$V,2,0)=E$7	text	NO

 

[samk379]

Hi and welcome to MrExcel.

Do you need this?

varios 08nov2024.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Barcode Reader	Start in B3
2
3	Order
4	Location
5
6
7		Order Number	Date	Due Date	CNCPLASMA	DEBURR	PRESSBRAKE	SAW	LATHE1	LATHE2	MACHINING	DRILL	TACK	WELD1	WELD2	GRIND	PAINT	ASSEMBLY	COMPLETE		Order	Location
8		TFD0012																			TFD0012	CNCPLASMA
9		CHD0036																			CHD0036	DEBURR
10		GD0009																			GD0009	SAW
11		LE0012																			LE0012	PRESSBRAKE
12		RR0001																			RR0001	LATHE2
13		RD0001																			RD0001	DRILL
14		HT0001																			HT0001	MACHINING
15
16
Hoja6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E8:S1000	Expression	=COLUMN()<MATCH($V8,$A$7:$S$7,0)	text	NO
E8:S1000	Expression	=VLOOKUP($B8,$U:$V,2,0)=E$7	text	NO

I need something very similar. Some orders might not require every step, so you might have an order that only requires SAW, DRILL, WELD, and ASSEMBLY. I'd like it where it's green when it's at saw, then when it moves to drill, the saw column goes black and drill goes green, and so on.

 

[DanteAmor]

I need something very similar. Some orders might not require every step, so you might have an order that only requires SAW, DRILL, WELD, and ASSEMBLY. I'd like it where it's green when it's at saw, then when it moves to drill, the saw column goes black and drill goes green, and so on.

You can explain each example with an image.
Fill the cells manually with colors simulating conditional formatting.
If you have exceptional cases, then explain each of these cases with a different image.

 

[samk379]

You can explain each example with an image.
Fill the cells manually with colors simulating conditional formatting.
If you have exceptional cases, then explain each of these cases with a different image.

Work Order Status Sheet.XLSX
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Barcode Reader	Start in B3
2
3	Order
4	Location
5
6
7		Order Number	Date	Due Date	CNCPLASMA	DEBURR	PRESSBRAKE	SAW	LATHE1	LATHE2	MACHINING	DRILL	TACK	WELD1	WELD2	GRIND	PAINT	ASSEMBLY	COMPLETE		Order	Location
8		TFD0012																			TFD0012	COMPLETE
9		CHD0036																			CHD0036	ASSEMBLY
10		GD0009																			GD0009	COMPLETE
Reader

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R8:R1613	Expression	=V8="ASSEMBLY"	text	NO
Q8:Q1613	Expression	=V8="PAINT"	text	NO
S8:S1613	Expression	=V8="COMPLETE"	text	NO
P8:P1613	Expression	=V8="GRIND"	text	NO
O8:O1613	Expression	=V8="WELD2"	text	NO
N8:N1613	Expression	=V8="WELD1"	text	NO
M8:M1613	Expression	=V8="TACK"	text	NO
L8:L1613	Expression	=V8="DRILL"	text	NO
K8:K1613	Expression	=V8="MACHINING"	text	NO
J8:J1613	Expression	=V8="LATHE2"	text	NO
I8:I1613	Expression	=V8="LATHE1"	text	NO
H8:H1613	Expression	=V8="SAW"	text	NO
G8:G1613	Expression	=V8="PRESSBRAKE"	text	NO
F8:F1613	Expression	=V8="DEBURR"	text	NO
E8:E1613	Expression	=V8="CNCPLASMA"	text	NO

I would like it so it only fills in the steps that were required for the job, rather than every step before the current step, if possible.