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]

BTW, I named my Barcode cell BarcodeScan. You should do the same since the macro uses that

 

[samk379]

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

A code would be scanned once that would translate to the order number and be input into B3, and then the code for the location would be scanned and input into B4. We just use Code39 barcodes, so scanning basically works the same as typing in the order number and location into those cells

 

[Jeffrey Mahoney]

I think your going to have a problem then. I think your going to need a different barcode cell for the Order number and the order location

The auto macro is going to try to add new values to the DB after any entry into the BarcodeScan cell

This is how I see it. You have a barcode next to the OrderNum cell. Once that cell is populated, an event macro moves the cell to the Order location barcode cell and waits for the next barcode. Once the barcode for the Order location has been entered, the macro then adds the values to the DB. Make sense?

 

[Jeffrey Mahoney]

You need a table to convert the barcodes to the correct values so we can add formulas in the OrderNum and OrderLoc cells. That way you don't to interpret them manually

 

[samk379]

I think your going to have a problem then. I think your going to need a different barcode cell for the Order number and the order location

The auto macro is going to try to add new values to the DB after any entry into the BarcodeScan cell

This is how I see it. You have a barcode next to the OrderNum cell. Once that cell is populated, an event macro moves the cell to the Order location barcode cell and waits for the next barcode. Once the barcode for the Order location has been entered, the macro then adds the values to the DB. Make sense?

Right now, there is a macro that puts the first scan in B3, then the cell moves to B4 for the location, and then that information is put into a table showing the current location for that order. There is no BarcodeScan cell

 

[Jeffrey Mahoney]

Here is an example

SAMk379 Barcode Reader.xlsm
	A	B	C
1		Value	Barcode
2	Order Number:
3	Location:
Summary

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  
  Set i = Intersect(Range("OrderNumBC"), Target)    'Move cell to Order Locatin Barcode
  If Not i Is Nothing Then
    Range("OrderLocBC").Select
    Exit Sub
  End If
  
  
  Set i = Intersect(Range("OrderLocBC"), Target)    'Add values to DB
  If Not i Is Nothing Then
    If Range("OrderNum").Value <> "" And Range("OrderLoc").Value <> "" Then
      AddNewOrderToDB
    End If
    Exit Sub
  End If

End Sub

 

[Jeffrey Mahoney]

Can you show me an example of one of the barcodes

 

[samk379]

Can you show me an example of one of the barcodes

I made a Video that shows how this process works. I typed the codes in instead of scanning the barcodes, but the process is the same when scanning, since the scanner essentially works as a text input device. The codes are just the order number/location in Code39 font so that they are scannable. In other words, the code for MACHINING is "MACHINING". You can see that when I input the order and the location, that information is updated in the table on the right side. Excel Demo

 

[Jeffrey Mahoney]

Well, the video wasn't as valuable, but your description was good. Based on that, you have one barcode for Order number and one for order location. My last post should help with that.

At the beginning, the active cell is Order Number. You scan a code for that and the macro moves focus to Order Location. You scan another code and the macro delivers the new set of data to the DB table, erases the last values and selects the Order Number again to start the process all over.

I altered the code below

Put this in a standard module

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 = ""
    
  Sheets("Summary").Range("OrderNum").Select
  
  
End Sub

Replace any code you had for the Summary sheet with this

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  
  Set i = Intersect(Range("OrderNum"), Target)    'Move cell to Order Location
  If Not i Is Nothing Then
    Range("OrderLoc").Select
    Exit Sub
  End If
  
  
  Set i = Intersect(Range("OrderLoc"), Target)    'Add values to DB
  If Not i Is Nothing Then
    If Range("OrderNum").Value <> "" And Range("OrderLoc").Value <> "" Then
      AddNewOrderToDB
    End If
    Exit Sub
  End If

End Sub