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 THD0001 is at the WELD2 station, for example, the cell at the intersection of the THD0001 row and the WELD2 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? In other words, if the order goes from WELD2 to ASSEMBLY, the cell for WELD2 is filled black.
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.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
2
3	Order
4	Location
5
6
7		Order	Date	Due Date	DEBURR	CNCPLASMA	PRESSBRAKE	SAW	LATHE1	LATHE2	MACHINING	DRILL	TACK	WELD1	WELD2	GRIND	PAINT	ASSEMBLY	COMPLETE		Order	Location
8		TFD0012																			TFD0012	CNCPLASMA
9		CHD0036																			CHD0036	SAW
10		GD0009																			GD0009	MACHINING
11		LE0012																			LE0012	DEBURR
12		HK0001																			HK0001	DEBURR
13		THD0001																			THD0001	WELD2
Reader

Cell Formulas
Range	Formula
B8:B13	B8	=U8

Named Ranges
Name	Refers To	Cells
Orders	=OrdersTable[Order]	B8

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

 

[Jeffrey Mahoney]

Does this get you started?

Book2
	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
2
3	Order
4	Location
5
6
7		Order	Date	Due Date	DEBURR	CNCPLASMA	PRESSBRAKE	SAW	LATHE1	LATHE2	MACHINING	DRILL	TACK	WELD1	WELD2	GRIND	PAINT	ASSEMBLY	COMPLETE		Order	Location
8		TFD0012																			TFD0012	CNCPLASMA
9		CHD0036																			CHD0036	SAW
10		GD0009																			GD0009	MACHINING
11		LE0012																			LE0012	DEBURR
12		HK0001																			HK0001	DEBURR
13		THD0001																			THD0001	WELD2
Sheet1

Cell Formulas
Range	Formula
B8:B13	B8	=U8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E8:S13	Expression	=IFERROR(MATCH(VLOOKUP($B8,$U:$V,2,FALSE),F$7:$S$7,0)>0,FALSE)	text	NO
E8:S14	Expression	=VLOOKUP($B8,$U:$V,2,0)=E$7	text	NO

 

[Jeffrey Mahoney]

For the other part, the date would have to entered by a macro. A Worksheet_Change macro to test the entered values at Order and Location. Or a button to call a macro. What gets entered from the barcode reader, both Order and Location?

 

[samk379]

Does this get you started?

Book2
	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
2
3	Order
4	Location
5
6
7		Order	Date	Due Date	DEBURR	CNCPLASMA	PRESSBRAKE	SAW	LATHE1	LATHE2	MACHINING	DRILL	TACK	WELD1	WELD2	GRIND	PAINT	ASSEMBLY	COMPLETE		Order	Location
8		TFD0012																			TFD0012	CNCPLASMA
9		CHD0036																			CHD0036	SAW
10		GD0009																			GD0009	MACHINING
11		LE0012																			LE0012	DEBURR
12		HK0001																			HK0001	DEBURR
13		THD0001																			THD0001	WELD2
Sheet1

Cell Formulas
Range	Formula
B8:B13	B8	=U8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E8:S13	Expression	=IFERROR(MATCH(VLOOKUP($B8,$U:$V,2,FALSE),F$7:$S$7,0)>0,FALSE)	text	NO
E8:S14	Expression	=VLOOKUP($B8,$U:$V,2,0)=E$7	text	NO

Very close, but I need it so that it fills in only the steps that were completed before, rather than filling in every step before. The reason for this is that some jobs won't require every step, so it would be nice to see which jobs required which steps.

Work Order Status Sheet.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
2
3	Order
4	Location
5
6
7		Order	Date	Due Date	DEBURR	CNCPLASMA	PRESSBRAKE	SAW	LATHE1	LATHE2	MACHINING	DRILL	TACK	WELD1	WELD2	GRIND	PAINT	ASSEMBLY	COMPLETE		Order	Location
8		TFD0012																			TFD0012	CNCPLASMA
9		CHD0036																			CHD0036	SAW
10		GD0009																			GD0009	MACHINING
11		LE0012																			LE0012	DEBURR
12		HK0001																			HK0001	DEBURR
13		THD0001																			THD0001	WELD2
Reader

Cell Formulas
Range	Formula
B8:B13	B8	=U8

Named Ranges
Name	Refers To	Cells
Orders	=OrdersTable[Order]	B8

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

 

[Jeffrey Mahoney]

On my version all the cells to the left of a green cell are black. Please check your formula again

 

[samk379]

On my version all the cells to the left of a green cell are black. Please check your formula again

I understand, I just made a version of what I would like it to look like by manually filling cells. I'm just wondering if there's a type of formula that will make it look like the example I posted as the job moves along.

 

[Jeffrey Mahoney]

A couple of things:
This Conditional formula doesn't show the text right. It should be formatted black fill in the CF format. XL2BB is not working properly to show it.
=IFERROR(MATCH(VLOOKUP($B8,$U:$V,2,FALSE),F$7:$S$7,0)>0,FALSE)

And this is what you have for the formula
=COLUMN()<MATCH($V8,$A$7:$S$7,0)

Please add in the CF formula I provided for the black cells

Book2
	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
2
3	Order
4	Location
5
6
7		Order	Date	Due Date	DEBURR	CNCPLASMA	PRESSBRAKE	SAW	LATHE1	LATHE2	MACHINING	DRILL	TACK	WELD1	WELD2	GRIND	PAINT	ASSEMBLY	COMPLETE		Order	Location
8		TFD0012																			TFD0012	CNCPLASMA
9		CHD0036																			CHD0036	SAW
10		GD0009																			GD0009	MACHINING
11		LE0012																			LE0012	DEBURR
12		HK0001																			HK0001	COMPLETE
13		THD0001																			THD0001	WELD2
Sheet1

Cell Formulas
Range	Formula
B8:B13	B8	=U8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E8:S13	Expression	=VLOOKUP($B8,$U:$V,2,0)=E$7	text	NO
E8:S13	Expression	=IFERROR(MATCH(VLOOKUP($B8,$U:$V,2,FALSE),F$7:$S$7,0)>0,FALSE)	text	NO

 

[samk379]

A couple of things:
This Conditional formula doesn't show the text right. It should be formatted black fill in the CF format. XL2BB is not working properly to show it.
=IFERROR(MATCH(VLOOKUP($B8,$U:$V,2,FALSE),F$7:$S$7,0)>0,FALSE)

And this is what you have for the formula
=COLUMN()<MATCH($V8,$A$7:$S$7,0)

Please add in the CF formula I provided for the black cells

Book2
	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
2
3	Order
4	Location
5
6
7		Order	Date	Due Date	DEBURR	CNCPLASMA	PRESSBRAKE	SAW	LATHE1	LATHE2	MACHINING	DRILL	TACK	WELD1	WELD2	GRIND	PAINT	ASSEMBLY	COMPLETE		Order	Location
8		TFD0012																			TFD0012	CNCPLASMA
9		CHD0036																			CHD0036	SAW
10		GD0009																			GD0009	MACHINING
11		LE0012																			LE0012	DEBURR
12		HK0001																			HK0001	COMPLETE
13		THD0001																			THD0001	WELD2
Sheet1

Cell Formulas
Range	Formula
B8:B13	B8	=U8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E8:S13	Expression	=VLOOKUP($B8,$U:$V,2,0)=E$7	text	NO
E8:S13	Expression	=IFERROR(MATCH(VLOOKUP($B8,$U:$V,2,FALSE),F$7:$S$7,0)>0,FALSE)	text	NO

I got it to the point where you did, with each cell to the left of the green cell filled black. If this is all that is possible, that's fine, but I'm wondering if there's a way to make it so that only the cells that were previously filled green are filled black. For example, a job that went through DEBURR, MACHINING, WELD1, ASSEMBLY, and COMPLETE, would have COMPLETE filled in green and DEBURR, MACHINING, WELD1, and ASSEMBLY would be filled in black, and the steps that weren't utilized are left blank.

Work Order Status Sheet.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
2
3	Order
4	Location
5
6
7		Order	Date	Due Date	DEBURR	CNCPLASMA	PRESSBRAKE	SAW	LATHE1	LATHE2	MACHINING	DRILL	TACK	WELD1	WELD2	GRIND	PAINT	ASSEMBLY	COMPLETE		Order	Location
8		TFD0012																			TFD0012	CNCPLASMA
9		CHD0036																			CHD0036	SAW
10		GD0009																			GD0009	MACHINING
11		LE0012																			LE0012	DEBURR
12		HK0001																			HK0001	DEBURR
13		THD0001																			THD0001	WELD2
Reader

Cell Formulas
Range	Formula
B8:B13	B8	=U8

Named Ranges
Name	Refers To	Cells
Orders	=OrdersTable[Order]	B8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E8:S1612	Expression	=VLOOKUP($B8,$U:$V,2,0)=E$7	text	NO
E8:S1612	Expression	=IFERROR(MATCH(VLOOKUP($B8,$U:$V,2,FALSE),F$7:$S$7,0)>0,FALSE)	text	NO

 

[Jeffrey Mahoney]

Are you keeping that history in columns A:V (Order / Location)?

Yes, it is possible.

Just between you, me, and the fence post (and maybe a million people lookng at this); I never ask if Excel can do it, I assume that it can.

 

[samk379]

Are you keeping that history in columns A:V (Order / Location)?

Yes, it is possible.

Just between you, me, and the fence post (and maybe a million people lookng at this); I never ask if Excel can do it, I assume that it can.

I'm not keeping that history, how can I do that?