Adding an if formula to existing VBA code

[Olympus64]

Hi!!! I am lost on trying to add an "if" statement to an existing VBA code. I would =IF(OR(G3="",H3=""),"-",IF(G3=H3,"Ship","Do Not Ship")) to
the VBA code below
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
For x = 2 To 100
If Cells(x, 1).Value <> "" And Cells(x, 2).Value = "" Then
Cells(x, 2).Value = Date & " " & Time

Cells(x, 2).NumberFormat = "m/d/yyyy h:mm AM/PM"
End If
Next
Range("B:B").EntireColumn.AutoFit

End Sub

 

[offthelip]

the way I would do this is to write a standard module. ( I have called it test). This make it much easier to debug the software while you are devleoping it. Otherwise every change runs the code. Then once it is working you can call the module from the workhseet change event. I have used variant arrays in this code to make it as fast a possible since it is run every time you change the worksheet this can be critical.
in a standard module:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
outarr = Range(Cells(1, 3), Cells(lastrow, 4))
 For i = 2 To lastrow
  If inarr(i, 1) = inarr(i, 2) And inarr(i, 1) <> "" Then
    outarr(i, 1) = "Ship"
    outarr(i, 2) = Now()
  Else
    outarr(i, 1) = "Do Not Ship"
    outarr(i, 2) = ""
  End If
 Next i
Application.EnableEvents = False
Range(Cells(1, 3), Cells(lastrow, 4)) = outarr
Range(Cells(1, 4), Cells(lastrow, 4)).NumberFormat = "m/d/yyyy h:mm AM/PM"
Application.EnableEvents = True
 
  
End Sub

in the worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Call test
End Sub

If you want to color them I suggest you use conditional formatting

 

[Olympus64]

the way I would do this is to write a standard module. ( I have called it test). This make it much easier to debug the software while you are devleoping it. Otherwise every change runs the code. Then once it is working you can call the module from the workhseet change event. I have used variant arrays in this code to make it as fast a possible since it is run every time you change the worksheet this can be critical.
in a standard module:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
outarr = Range(Cells(1, 3), Cells(lastrow, 4))
 For i = 2 To lastrow
  If inarr(i, 1) = inarr(i, 2) And inarr(i, 1) <> "" Then
    outarr(i, 1) = "Ship"
    outarr(i, 2) = Now()
  Else
    outarr(i, 1) = "Do Not Ship"
    outarr(i, 2) = ""
  End If
 Next i
Application.EnableEvents = False
Range(Cells(1, 3), Cells(lastrow, 4)) = outarr
Range(Cells(1, 4), Cells(lastrow, 4)).NumberFormat = "m/d/yyyy h:mm AM/PM"
Application.EnableEvents = True
 
 
End Sub

in the worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Call test
End Sub

If you want to color them I suggest you use conditional formatting

Offthelip you killed it!! This is exactly what I needed!! Wow!!! Thank you so much. On a side note how can I keep the date and time static in order to retain past date and time stamp information. I want to be able to have history of scans relative to the date and time scanned.

 

[offthelip]

try this slight modification:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
outarr = Range(Cells(1, 3), Cells(lastrow, 4))
 For i = 2 To lastrow
  If outarr(i, 2) = "" Then               ' then line added
    If inarr(i, 1) = inarr(i, 2) And inarr(i, 1) <> "" Then
      outarr(i, 1) = "Ship"
      outarr(i, 2) = Now()
    Else
      outarr(i, 1) = "Do Not Ship"
      outarr(i, 2) = ""
    End If
  End If   ' and this line
 Next i
Application.EnableEvents = False
Range(Cells(1, 3), Cells(lastrow, 4)) = outarr
Range(Cells(1, 4), Cells(lastrow, 4)).NumberFormat = "m/d/yyyy h:mm AM/PM"
Application.EnableEvents = True
 
   
End Sub

 

[Olympus64]

try this slight modification:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
outarr = Range(Cells(1, 3), Cells(lastrow, 4))
 For i = 2 To lastrow
  If outarr(i, 2) = "" Then               ' then line added
    If inarr(i, 1) = inarr(i, 2) And inarr(i, 1) <> "" Then
      outarr(i, 1) = "Ship"
      outarr(i, 2) = Now()
    Else
      outarr(i, 1) = "Do Not Ship"
      outarr(i, 2) = ""
    End If
  End If   ' and this line
 Next i
Application.EnableEvents = False
Range(Cells(1, 3), Cells(lastrow, 4)) = outarr
Range(Cells(1, 4), Cells(lastrow, 4)).NumberFormat = "m/d/yyyy h:mm AM/PM"
Application.EnableEvents = True
 
  
End Sub

Worked Perfect!!!! Thank you!!!

 

[Olympus64]

Worked Perfect!!!! Thank you!!!

@offthelip how can I modify the code to add more columns? I would like to 1 more column.


Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
outarr = Range(Cells(1, 3), Cells(lastrow, 4))
For i = 2 To lastrow
If outarr(i, 2) = "" Then ' then line added
If inarr(i, 1) = inarr(i, 2) And inarr(i, 1) <> "" Then
outarr(i, 1) = "Ship"
outarr(i, 2) = Now()
Else
outarr(i, 1) = "Do Not Ship"
outarr(i, 2) = ""
End If
End If ' and this line
Next i
Application.EnableEvents = False
Range(Cells(1, 3), Cells(lastrow, 4)) = outarr
Range(Cells(1, 4), Cells(lastrow, 4)).NumberFormat = "m/d/yyyy h:mm AM/PM"
Application.EnableEvents = True


End Sub

Barcode Label 1	Barcode Lable 2	Barcode Label 3	Status	Date and time
12​	12​		Ship	10/20/2021 1:54 PM​
12​	12​		Ship	10/20/2021 1:54 PM​
13​	13​		Ship	10/20/2021 1:54 PM​
15-100633-00	15-100633-00		Ship	10/20/2021 1:54 PM​
15-100633-00	15-100633-00		Ship	10/20/2021 1:54 PM​
13​	13​		Ship	10/20/2021 1:54 PM​
13​	13​		Ship	10/20/2021 1:54 PM​
12​	12​		Ship	10/20/2021 1:54 PM​
12​	34​		Do Not Ship
15-048854-00	15-048854-00		Ship	10/20/2021 1:54 PM​
12​	12​		Ship	10/20/2021 1:56 PM​
12​	12​		Ship	10/20/2021 1:57 PM​
12​	12​		Ship	10/20/2021 1:58 PM​
12​	12​		Ship	10/20/2021 2:01 PM​
12​	12​		Ship	10/20/2021 2:03 PM​
13​	13​		Ship	10/20/2021 5:15 PM​

 

[offthelip]

I am not at a PC at the moment, so limited . You can change the number of columns for the input by changing the second number in the range addressing:
Inarr=range(cells(1,1),cells(last row,2)) addresses columns A and B, changing this to inarr=range(1,1),cells(last row,3)) changes this to columns A,B and C. Note you must change the outarr definition as well and you must do in all places.