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]

So, I can't think of a way to mark the cells you want unless you record each the steps that have been processed. When you scan in your readings, you'll need to add the new status to the end of the list with Order and Location; not just the last status.

Of course this is a different part of the same thing. I would save those to a table on it's own sheet. Need some VBA code?

 

[samk379]

So, I can't think of a way to mark the cells you want unless you record each the steps that have been processed. When you scan in your readings, you'll need to add the new status to the end of the list with Order and Location; not just the last status.

Of course this is a different part of the same thing. I would save those to a table on it's own sheet. Need some VBA code?

Sure. I'm open to trying anything.

 

[Jeffrey Mahoney]

OK I have revised this a lot. I put formulas in the cells instead of the Conditional Formatting (CF). I used CF to look at the values of the cells.
-1 = no color
0 = Green
>0 = Black

CF formulas really slowed things down. Excel handles formulas in cells much better.

I created a sheet called DB and am storing the history of each order / location combo. There is some code below that you can run either with a button or automatically if you choose to have it add those to the DB when the Barcode Reader cell gets populated with a new value. The values showing in cells E8:S13 can be hidden with a number format like ";;;".

SAMk379 Barcode Reader.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
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
8		TFD0012			-1	3	-1	2	-1	-1	1	-1	-1	-1	-1	-1	-1	-1	0
9		CHD0036			-1	-1	-1	0	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1
10		GD0009			-1	-1	-1	-1	-1	-1	0	-1	-1	-1	-1	-1	-1	-1	-1
11		LE0012			1	-1	0	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1
12		HK0001			2	-1	-1	-1	-1	-1	-1	1	-1	-1	-1	-1	-1	-1	0
13		THD0001			-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	0	-1	-1	-1	-1
14
Summary

Cell Formulas
Range	Formula
E8:S13	E8	=IF(COUNTIFS(DB!$A2:$A10000,$B8,DB!$B2:$B10000,E$7)>0,SUM(COUNTIFS(DB!$A$2:$A$10000,$B8,DB!$B$2:$B$10000,F$7:$T$7)),-1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E8:S13	Expression	=E8>0	text	NO
E8:S13	Expression	=E8=0	text	NO

SAMk379 Barcode Reader.xlsm
	A	B	C
1	Order	Location	Date Time
2	TFD0012	CNCPLASMA	5/1/2024 12:12
3	CHD0036	SAW	5/2/2024 13:24
4	GD0009	MACHINING	5/3/2024 14:36
5	LE0012	DEBURR	5/4/2024 15:48
6	HK0001	DEBURR	5/5/2024 17:00
7	THD0001	WELD2	5/6/2024 18:12
8	TFD0012	SAW	5/1/2024 14:30
9	TFD0012	MACHINING	5/1/2024 16:01
10	LE0012	PRESSBRAKE	5/4/2024 16:50
11	HK0001	DRILL	5/5/2024 18:10
12	HK0001	COMPLETE	5/6/2024 08:00
13	TFD0012	COMPLETE	6/1/2024 09:00
14	JCK0002	DEBURR	12/19/2024 10:34
DB

Sub AddNewOrderToDB()

  Dim Cel As Range
  Dim OrderRng As Range
  Dim OrderNum As String
  Dim OrderLoc As String
  
  OrderNum = Sheets("Summary").Range("Ordernum").Value
  OrderLoc = Sheets("Summary").Range("Orderloc").Value
  
  Set OrderRng = Sheets("DB").Range("OrderHist_Tbl[Order]")
  Set Cel = OrderRng.Resize(1, 1).Offset(OrderRng.Rows.Count, 0)
  Cel.Value = OrderNum
  Cel.Offset(0, 1).Value = OrderLoc
  Cel.Offset(0, 2).Value = Now()
  
  Sheets("Summary").Range("Ordernum").Value = ""
  Sheets("Summary").Range("Orderloc").Value = ""
  
  
End Sub

 

[samk379]

This looks like it's gonna be the solution! I do have a few questions, though.
1. Do I put the code under the DB sheet or for the entire workbook?
2. Do I need to create new ranges for orders and locations, if so, which cells do you have those ranges covering?
Thank you!

 

[Jeffrey Mahoney]

The code can be put in the Sheet module, but I would put it in a standard module.

I created named ranges for the Order Number called OrderNum and Order Location called OrderLoc. The VBA code is referencing those named ranges

The DB sheet table is an Excel table and I called it OrderHist_Tbl. The code is also referencing that.

Matter of fact, while I was writing this, I think the formula in Cell E8 should be: =IF(COUNTIFS(OrderHist_Tbl[Order],$B8,OrderHist_Tbl[Location],E$7)>0,SUM(COUNTIFS(OrderHist_Tbl[Order],$B8,OrderHist_Tbl[Location],F$7:$T$7)),-1)

And then copy that down and over.

 

[samk379]

I'm having some trouble implementing it into the sheet. Would you mind sending a screenshot of your name manager, so that I know what names reference which ranges? It also could be an issue with the VBA code I previously had not interacting correctly with the new code.

 

[samk379]

I'm having some trouble implementing it into the sheet. Would you mind sending a screenshot of your name manager, so that I know what names reference which ranges? It also could be an issue with the VBA code I previously had not interacting correctly with the new code.

It looks like the Summary table is reading off of the DB table, but I can't get the information from the scanners to input into the DB table.

 

[Jeffrey Mahoney]

Ok, I assumed you could create a macro button to run that code manually. Do you want some code to automatically put the Order number and location into the DB when a new entry is added to the Barcode cell?

 

[samk379]

Ok, I assumed you could create a macro button to run that code manually. Do you want some code to automatically put the Order number and location into the DB when a new entry is added to the Barcode cell?


View attachment 120518

That would be ideal, if possible.

 

[Jeffrey Mahoney]

Add this code to the sheet module, in my case I named it Summary

How do you get the OrderNum and OrderLoc cells to populate the values you want from the barcode?

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
 
  Set i = Intersect(Range("BarcodeScan"), Target)
  If Not i Is Nothing Then
    If Range("OrderNum").Value <> "" And Range("OrderLoc").Value <> "" Then
      AddNewOrderToDB
    End If
  End If
 
End Sub