VBA conditions clashed with one another

[alisoncleverly]

Hi everyone,

I have this file that tracks engine status from Sales and Production departments.

FYI my file tracks the last 8 days of the month, each Day group has the following column order: Sales, Production, Day #, Status.
1st Sales column is column N
2nd Sales column is column R
3rd Sales column is column V
4th Sales column is column Z
5th Sales column is column AD
6th Sales column is column AH
7th Sales column is column AL
8th Sales column is column AP

Previously, DanteAmor helped me out with one of my conditions (See here) and thanks to him, everything worked out perfectly (thanks again Dante). I presented the file to my manager and now she wanted to change the conditions in the codes where:

a) If the value in 47th column is "Shipped", all empty Sales and Production cells should become "Rollup".
Currently this piece of codes does just that (but using using the "ByVal Target As Range"

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, r1 As Range, lastColumn As Long, counter As Long
  Dim MaxCol As Variant, rg As Range, j As Long

'Shipped without Title Transfer
lastColumn = Me.Cells(1, Me.Columns.Count).End(xlToLeft).Column
  If Me.Cells(1, Target.Column).Value = "MB51 Shipped" Then
    For counter = 1 To lastColumn
      If (Me.Cells(1, counter).Value = "Sales" Or Me.Cells(1, counter).Value = "Production") And IsEmpty(Me.Cells(Target.Row, counter).Value) Then
        Me.Cells(Target.Row, counter).Value = "Rollup"
      End If
    Next counter
  End If

Now, my manager wanted to only consider explicitly the value of the cell and not trigger the macro every time the cell changes (because someone in the team might make a mistake, putting "shipped" then change it later but the codes won't automatically delete the "Rollup" filled in when cell in the 47th column was triggered).

Also, I feel like there's more room to improve the above codes. Please advise if you can. I'm not a programmer so my coding knowledge is very limited, hope you can help out with this.

b) this is the second request where I found out the piece of codes I wrote in a) above clashed with what Dante's codes do.
As mentioned earlier, I have 8 Sales columns in total, so she wanted, consider we're referring the scenario happening in the same row, if the last "Sales" column has value "Title Transfer" then cell in 50th column will have an "x". And if later on, the last Sales cell changes its status to anything different than "Title Transfer", the "x" will automatically disappear and reappear only when the last "Sales" cell has "Title Transfer" => This DanteAmor helped me out and his codes work amazingly! (see here)
Ex:
3rd "Sales" cell has "Title Transfer" then "x" in cell of 50th column
4th "Sales cell has "Red" then "x" disappears in cell of 50th column
5th "Sales" cell has "Title Transfer" then "x" in cell of 50th column and so on and so forth.

Now the problem is (see my sample file Row2, 4, 6):

1. First, in row #2, I had "Title Transfer" in the 3rd Sales column (representing Day 3), which then the Macro automatically returned an "x" in the 50th column (which is awesome and correct).
2. Then, the next day I came back and filled in the status for Sales and Production columns of Day 4, then after that, I put "Shipped" in the 47th column, which the Macro automatically fill in "Rollup" for all empty "Sales" and "Production" cells (Day 5 - Day 8), which again is correct. BUT, the problem is, my "x" in the 50th column disappeared which is not right. I really need it to stay where it was and not disappear like it currently does. Can anyone please help with this? (See rows 2 and 4 in my sample file)

So to be short,

If "Shipped" in column 47th, all empty "Sales" and "Production" cells become "Rollup"

If "Title Transfer" in the last Sales column (regardless of the column order, can be 2nd Sales column, can be the 5th, can be the 8th), "x" in 50th column

If "Title Transfer" in the last Sales column (happens first and before the 2nd condition) (automatically has an "x" in 50th column) AND "Shipped" in 47th column then "Shipped" in all empty "Sales" and "Production" cells. (See row 6 in my sample file)

[Note: can never have "Shipped' in 47th column first then "Title Transfer" in Sales column and "x" in 50th column later. I meant, once something has "Shipped", it's the final status and doesn't make sense to have a different status like the item is still with us.]

So none of these conditions should clash with the other (which I know my current file absolutely has this huge problem)

I know this is pretty complicated and my limited coding ability can't do much about. But please help me out! Thanks a lot in advance! Please let me know if anything is not clear

 

[alisoncleverly]

Really hope somebody can help me out with this...