Setting up a PO tracking file

[crazyeyeschase]

I have taken on more responsibility at work and decided I need a better way of tracking PO's and want to do so with macros within Excel.

Its been a while since i played with macros so I'm needing some help and refresher.
In this I want to ask 3 separate questions as I'm refreshing myself and before I get to deep in the weeds with extra worksheets.

1. Column J and K (goods received and invoice received) get an "x" once I have received receipt of the goods/invoice for the PO. Column L lists an ETA for completion. Once columns J and K both show the value "X" I want L to change value to "complete"

I have wrote the following which works but I need help running it all the way thru the worksheet.

Sub if_complete()
    If Range("J2").Value = "x" Then
        If Range("k2").Value = "x" Then
            Range("l2").Value = "complete"
         End If
    End If
End Sub

2. I need a way to format each PO separately from the last to help define each PO on the spreadsheet. The PO is listed in column A however each line item on each PO is also listed.
So for example one PO might be 1 line item and only 1 row in excel but the next might be 5 line items and 5 rows in excel.

How can I format say every other PO to have a gray background but it also changes each duplicate.

3. How can i have macros running when the sheet is updated?

I have much more I want to do such as a dialogue box to insert new PO's that will reference vendor names and their vendor number, separate sheets for open/closed PO's and sheets for each building as I manage 3 (and growing)

This is just a start and would be something i could share across the team any help and guidance would be great.

 

[Micron]

If you are a DIY sort of person (you seem to be) then
1) research how to loop over whatever (sheets, columns, rows, range) as required. Very useful.
2) not sure - probably conditional formatting - e.g. a condition like (in my own words, "if PO cell <> Offset(-1,0) set interior colour = yellow")
3) research sheet events, or in the vb editor, dbl click a sheet in the objects pane, then use the top left drop down to select sheet (not "General") then look at the list of sheet events. Likely candidate is sheet change event.

4) OK, an answer/suggestion even though there was no 4th question: This is a job for a database, but I doubt you want to go there.

 

[crazyeyeschase]

If you are a DIY sort of person (you seem to be) then
1) research how to loop over whatever (sheets, columns, rows, range) as required. Very useful.
2) not sure - probably conditional formatting - e.g. a condition like (in my own words, "if PO cell <> Offset(-1,0) set interior colour = yellow")
3) research sheet events, or in the vb editor, dbl click a sheet in the objects pane, then use the top left drop down to select sheet (not "General") then look at the list of sheet events. Likely candidate is sheet change event.

4) OK, an answer/suggestion even though there was no 4th question: This is a job for a database, but I doubt you want to go there.

Thanks for the info.

Here is where i am at currently.

Sub completeed_loop()
    Dim LR As Long
    Dim cell As Range
    Dim GR As Long 'goods receipt
    Dim IR As Long 'invocie receipt
    Dim EC As Long 'est completion date
    
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    GR = Range("J2" & Selection.Row)
    IR = Range("K2" & Selection.Row)
    EC = Range("L2" & Selection.Row)
    
    For Each cell In Range("A2:A" & LR)
        If GR = IR Then
            EC = "Complete"
        End If
    Next cell
End Sub

Im having trouble calling the same row for each column in the command.

 

[crazyeyeschase]

And another version that doesn't work. Ill figure it out.

Sub test()
    Dim LR As Long
    Dim cell As Range
    Dim GR As Range 'goods receipt
    
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    GR = Range("J2:J" & LR)

    For Each cell In GR
        If (cell.Value = "x") Then
                If Activecerll.Offset(0, 1).Value = "x" Then
                    ActiveCell.Offset(0, 1).Value = "Complete"
                    Exit For
                End If
        End If
    Next cell
End Sub

 

[crazyeyeschase]

I figured it out. It didn't like me using GR as Long or range so i just removed it.

Sub completion()
    Dim LR As Long
    Dim C As Range
    
    LR = Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False
    For Each C In Range("J2:J" & LR)
        If C.Value = "x" Then
                If C.Offset(0, 1).Value = "x" Then
                    C.Offset(0, 2).Value = "Complete"
                End If
        End If
    Next C
Application.ScreenUpdating = True
End Sub

 

[Micron]

Glad to see you got it solved!