For the code below, instead of using 'For Each cell In rngG, I want the code to focus…
- User
For the code below, instead of using 'For Each cell In rngG, I want the code to focus only on the "Target Cell" and the Target Row
Set rngG = Intersect(Target, Range("G6:G500")) If Not rngG Is Nothing Then For Each cell In rngG If cell.Value = "" Then 'End If Exit Sub 'ElseIf IsNumeric(cell.Value) Then If IsNumeric(cell.Value) Then If IsDate(cell.Offset(0, 2).Value) = False Then MsgBox "Date Service Completed not present" End If End If Exit Sub 'ElseIf cell.Offset(0, -5).Value <> "Service" Then If IsNumeric(cell.Value) Then If cell.Offset(0, -5).Value <> "Service" Then MsgBox "Service Schedule Days must ONLY be entered for Services" End If Exit Sub 'Else If IsNumeric(cell.Value) Then If IsDate(cell.Offset(0, 2).Value) <> False And cell.Offset(0, -5).Value = "Service" Then MsgBox "A New Service row will now be created" cell.Offset(0, -5).Value = "Serviced" cell.Offset(0, 1).Value = "" Dim newRow As Long newRow = Cells(Rows.Count, "B").End(xlUp).Row + 1 Range("B" & newRow).Value = "Service" Range("C" & newRow).Value = cell.Offset(0, 2).Value Range("F" & newRow).Value = cell.Offset(0, -1).Value Range("J" & newRow).Value = cell.Offset(0, 3).Value Range("L" & newRow).Value = cell.Offset(0, 5).Value Range("H" & newRow).Value = cell.Offset(0, 2).Value + cell.Value Exit Sub End If End If Next cell End IfExit Sub
- Assistant
To focus only on the "Target Cell" and the Target Row, you can remove the 'For Each cell In rngG' loop and replace 'cell' with 'Target' in the code.
Here's the modified code:
Set rngG = Intersect(Target, Range("G6:G500")) If Not rngG Is Nothing Then If rngG.Value = "" Then Exit Sub ElseIf IsNumeric(rngG.Value) Then If IsDate(rngG.Offset(0, 2).Value) = False Then MsgBox "Date Service Completed not present" End If Exit Sub ElseIf IsNumeric(rngG.Value) Then If rngG.Offset(0, -5).Value <> "Service" Then MsgBox "Service Schedule Days must ONLY be entered for Services" End If Exit Sub ElseIf IsNumeric(rngG.Value) Then If IsDate(rngG.Offset(0, 2).Value) <> False And rngG.Offset(0, -5).Value = "Service" Then MsgBox "A New Service row will now be created" rngG.Offset(0, -5).Value = "Serviced" rngG.Offset(0, 1).Value = "" Dim newRow As Long newRow = Cells(Rows.Count, "B").End(xlUp).Row + 1 Range("B" & newRow).Value = "Service" Range("C" & newRow).Value = rngG.Offset(0, 2).Value Range("F" & newRow).Value = rngG.Offset(0, -1).Value Range("J" & newRow).Value = rngG.Offset(0, 3).Value Range("L" & newRow).Value = rngG.Offset(0, 5).Value Range("H" & newRow).Value = rngG.Offset(0, 2).Value + rngG.Value Exit Sub End If End If End If Exit SubNote: The code assumes that there is only one cell selected as the "Target Cell". If multiple cells are selected, the code will only focus on the first cell in the selection.
- User
For the code below, instead of using 'For Each cell In rngI, I want the code to focus only on the "Target Cell" and the Target Row
Set rngI = Intersect(Target, Range("I6:I500")) If Not rngI Is Nothing Then For Each cell In rngI If IsDate(cell.Value) Then If cell.Offset(0, -7).Value = "Service" Then MsgBox "Thank you for entering Service Date Completed. Please proceed to enter Service Schedule as number of days." cell.Offset(0, -2).Select Exit Sub Else If cell.Offset(0, -7).Value <> "Service" Then If Not doubleClickFlag Then MsgBox "Please proceed to change the Issue description ( column B ) as required." cell.Offset(0, -7).Select End If End If Exit For End If End If Next cell doubleClickFlag = FalseExit Sub
- Assistant
To focus only on the "Target Cell" and the Target Row, you can remove the 'For Each cell In rngI' loop and replace 'cell' with 'Target' in the code.
Here's the modified code:
Set rngI = Intersect(Target, Range("I6:I500")) If Not rngI Is Nothing Then If IsDate(rngI.Value) Then If rngI.Offset(0, -7).Value = "Service" Then MsgBox "Thank you for entering Service Date Completed. Please proceed to enter Service Schedule as number of days." rngI.Offset(0, -2).Select Exit Sub Else If rngI.Offset(0, -7).Value <> "Service" Then If Not doubleClickFlag Then MsgBox "Please proceed to change the Issue description (column B) as required." rngI.Offset(0, -7).Select End If End If End If End If End If doubleClickFlag = False Exit SubNote: The code assumes that there is only one cell selected as the "Target Cell". If multiple cells are selected, the code will only focus on the first cell in the selection.
- User
For the code below, instead of using 'For Each cell In rngI, I want the code to focus only on the "Target Cell" and the Target Row
Set rngI = Intersect(Target, Range("I6:I500")) If Not rngI Is Nothing Then For Each cell In rngI If IsDate(cell.Value) Then If cell.Offset(0, -7).Value = "Service" Then MsgBox "Thank you for entering Service Date Completed. Please proceed to enter Service Schedule as number of days." cell.Offset(0, -2).Select Exit Sub Else If cell.Offset(0, -7).Value <> "Service" Then If Not doubleClickFlag Then MsgBox "Please proceed to change the Issue description ( column B ) as required." cell.Offset(0, -7).Select End If End If Exit For End If End If Next cell doubleClickFlag = FalseExit Sub
- Assistant
To focus only on the "Target Cell" and the Target Row, you can remove the 'For Each cell In rngI' loop and replace 'cell' with 'Target' in the code.
Here's the modified code:
Set rngI = Intersect(Target, Range("I6:I500")) If Not rngI Is Nothing Then If IsDate(rngI.Value) Then If rngI.Offset(0, -7).Value = "Service" Then MsgBox "Thank you for entering Service Date Completed. Please proceed to enter Service Schedule as number of days." rngI.Offset(0, -2).Select Exit Sub Else If rngI.Offset(0, -7).Value <> "Service" Then If Not doubleClickFlag Then MsgBox "Please proceed to change the Issue description (column B) as required." rngI.Offset(0, -7).Select End If End If Exit For End If End If End If doubleClickFlag = False Exit SubNote: The code assumes that there is only one cell selected as the "Target Cell". If multiple cells are selected, the code will only focus on the first cell in the selection.
- User
When I enter a number into any cell in column 'G' the code below is not triggered.
Set rngG = Intersect(Target, Range("G6:G500")) If Not rngG Is Nothing Then doubleClickFlag = False If rngG.Value = "" Then Exit Sub ElseIf IsNumeric(rngG.Value) Then If IsDate(rngG.Offset(0, 2).Value) = False Then MsgBox "Date Service Completed not present" End If Exit Sub ElseIf IsNumeric(rngG.Value) Then If rngG.Offset(0, -5).Value <> "Service" Then MsgBox "Service Schedule Days must ONLY be entered for Services" End If Exit Sub ElseIf IsNumeric(rngG.Value) Then If IsDate(rngG.Offset(0, 2).Value) <> False And rngG.Offset(0, -5).Value = "Service" Then MsgBox "A New Service row will now be created" rngG.Offset(0, -5).Value = "Serviced" rngG.Offset(0, 1).Value = "" Dim newRow As Long newRow = Cells(Rows.Count, "B").End(xlUp).Row + 1 Range("B" & newRow).Value = "Service" Range("C" & newRow).Value = rngG.Offset(0, 2).Value Range("F" & newRow).Value = rngG.Offset(0, -1).Value Range("J" & newRow).Value = rngG.Offset(0, 3).Value Range("L" & newRow).Value = rngG.Offset(0, 5).Value Range("H" & newRow).Value = rngG.Offset(0, 2).Value + rngG.Value Exit Sub End If End If End If Exit Sub
- Assistant
The code you provided should be triggered when entering a number into any cell in column 'G'. However, there might be some issues with the code that prevent it from running correctly. Here are a few suggestions to troubleshoot the issue:
-
Make sure the code is placed in the appropriate event handler. Check that it is located within the worksheet module that corresponds to the sheet where you are entering the numbers in column 'G'. The code should be placed in the Worksheet_Change event.
-
Enable Events: In some cases, events may be temporarily disabled, which could prevent the code from running. To ensure events are enabled, you can add the line
Application.EnableEvents = Trueat the beginning of your code. -
Check for other conflicting code: If you have other event handlers or macros in the worksheet module, they may conflict with the code you provided. Temporarily disable any other code and test if the issue persists.
-
Review data validation: It's possible that there is data validation or other restrictions in place that prevent values from being entered in column 'G'. Review any data validation rules or conditional formatting that may be affecting the behavior.
If you're still experiencing issues, providing additional details or the full code in the worksheet module would be helpful to further investigate the problem.
-