On the same row of my active sheet, if column G has a value and column I has a date then…
- User
On the same row of my active sheet, if column G has a value and column I has a date then if I insert a date in column H of the same row then check If column G does not have a value, then pop up the message 'Service Schedule period not present" and Exit Sub check If column I does not have a value, then pop up the message 'Date Service completed not present" and Exit Sub If column G and I both have values then change the value of column B on the same row to the value 'Serviced'. Then find the next available empty row in the range B:J and change the cell values of the new row as described; Column B value = 'Service' Column C value = Date Value displayed in column H from the row where column B was recently changed to 'Serviced' Column G value = Value dispalyed in column G from the row where column B was recently changed to 'Serviced' Column H value = Value dispalyed in column H from the row where column B was recently changed to 'Serviced' Column J value = Value dispalyed in column J from the row where column B was recently changed to 'Serviced' Column L value = Value dispalyed in column L from the row where column B was recently changed to 'Serviced' Wait 1 second then pop up message "Serviced Due Date will be deleted and added to New Service row" Then in Column H of the same row where column B was recently changed to 'Serviced' change the value to ""
- Assistant
Here is the code you can use to achieve the desired functionalities:
Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim lastRow As Long Dim nextRow As Long Set ws = ActiveSheet 'check if column G has a value and column I has a date If Not Intersect(Target, ws.Range("G:G, I:I")) Is Nothing Then If ws.Cells(Target.Row, "G").Value <> "" And IsDate(ws.Cells(Target.Row, "I").Value) Then 'check if column H is populated If ws.Cells(Target.Row, "H").Value = "" Then 'check if column G does not have a value If ws.Cells(Target.Row, "G").Value = "" Then MsgBox "Service Schedule period not present" Exit Sub End If 'check if column I does not have a value If Not IsDate(ws.Cells(Target.Row, "I").Value) Then MsgBox "Date Service completed not present" Exit Sub End If 'change the value of column B to 'Serviced' ws.Cells(Target.Row, "B").Value = "Serviced" 'find the next available empty row in range B:J lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row nextRow = lastRow + 1 'copy cell values to new row ws.Cells(nextRow, "B").Value = "Service" ws.Cells(nextRow, "C").Value = ws.Cells(Target.Row, "H").Value ws.Cells(nextRow, "G").Value = ws.Cells(Target.Row, "G").Value ws.Cells(nextRow, "H").Value = ws.Cells(Target.Row, "H").Value ws.Cells(nextRow, "J").Value = ws.Cells(Target.Row, "J").Value ws.Cells(nextRow, "L").Value = ws.Cells(Target.Row, "L").Value 'wait 1 second Application.Wait (Now + TimeValue("0:00:01")) 'pop up message MsgBox "Serviced Due Date will be deleted and added to New Service row" 'change the value of column H to "" ws.Cells(Target.Row, "H").Value = "" End If End If End If End SubNote: This code should be placed in the Sheet module of the worksheet where you want it to run. To do this, right-click on the sheet tab, select "View Code," and paste the code into the code window that opens.
This code utilizes the Worksheet_Change event, which triggers whenever there are changes in the worksheet. It checks if the change happens in column G or I, and if both columns have values and meets the specified conditions, it performs the necessary operations. The code also includes error handling to display pop-up messages if certain conditions are not met.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.