L
L. Howard Kittle
Hello Excel Expert and Users,
This code produces a column of Comment boxes on sheet "Your Notes" if a
certain value happens on another sheet, the value "L" or "LE".
I would like to start a new column of comment boxes if the comments reach
row 30 in column A. Move over to column E and put the comments there until
it reaches row 30 and move over to column I and then to column M.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim j As Integer
i = Target.Row
j = Target.Column
On Error Resume Next
If Intersect(Target, Range(j & ":" & i)) Is Nothing Then
Exit Sub
Else
If Intersect(Target, Range(j & ":" & i)).Value = "L" Then
Sheets("Your Notes").Activate
Sheets("Your Notes").Range("A100").End(xlUp).Offset(2, 0).Select
With Selection
.Value = "Late"
.AddComment
.Comment.Text Text:=Sheets("Main Sheet").Cells(12, j).Value & _
Chr(10) & Cells(i, "D").Value
.Comment.Visible = True
End With
End If
If Intersect(Target, Range(j & ":" & i)).Value = "LE" Then
Sheets("Your Notes").Activate
Sheets("Your Notes").Range("A100").End(xlUp).Offset(2, 0).Select
With Selection
.Value = "Left early"
.AddComment
.Comment.Text Text:=Sheets("Main Sheet").Cells(12, j).Value & _
Chr(10) & Cells(i, "D").Value
.Comment.Visible = True
End With
End If
End If
End Sub
Thanks for any help.
Regards,
Howard
This code produces a column of Comment boxes on sheet "Your Notes" if a
certain value happens on another sheet, the value "L" or "LE".
I would like to start a new column of comment boxes if the comments reach
row 30 in column A. Move over to column E and put the comments there until
it reaches row 30 and move over to column I and then to column M.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim j As Integer
i = Target.Row
j = Target.Column
On Error Resume Next
If Intersect(Target, Range(j & ":" & i)) Is Nothing Then
Exit Sub
Else
If Intersect(Target, Range(j & ":" & i)).Value = "L" Then
Sheets("Your Notes").Activate
Sheets("Your Notes").Range("A100").End(xlUp).Offset(2, 0).Select
With Selection
.Value = "Late"
.AddComment
.Comment.Text Text:=Sheets("Main Sheet").Cells(12, j).Value & _
Chr(10) & Cells(i, "D").Value
.Comment.Visible = True
End With
End If
If Intersect(Target, Range(j & ":" & i)).Value = "LE" Then
Sheets("Your Notes").Activate
Sheets("Your Notes").Range("A100").End(xlUp).Offset(2, 0).Select
With Selection
.Value = "Left early"
.AddComment
.Comment.Text Text:=Sheets("Main Sheet").Cells(12, j).Value & _
Chr(10) & Cells(i, "D").Value
.Comment.Visible = True
End With
End If
End If
End Sub
Thanks for any help.
Regards,
Howard