Reference a specific cell in the active row

[dweber1188]

I am trying to write a code that will generate a comment box if the user
enters a value not equal to the value I set. All my values are entered in
column B. How do I need to word the code so that if the active cell is
anywhere on row 4, the cell value is compared to B4, If the active cell is
M27 then its value is compared to B27, etc.

This is the portion of the code I am talking about.

If Target <> WHAT DO I PUT HERE??? Then
With Target
.AddComment
.Comment.Text Text:=Application.UserName & ":" & Chr(10)
.Comment.Visible = True
End With
End If

 

[L. Howard Kittle]

I may not have a solution but I have a question...

<... if the active cell is anywhere on row 4, the cell value is compared to
B4, If the active cell is M27 then its value is compared to B27, etc.>

If the activecell is anywhere in row 4 you want to campare to the value in
B4. And if the activecell is M27 you want to compare to B27?

Why the... etc.?

Did you mean activecell anywhere in row 27 compared to B27?

HTH
Regards,
Howard

 

[Rick Rothstein]

Try it this way...

If Target.Value <> Cells(Target.Row, "B").Value Then

 

[OssieMac]

Try the following. (I have assumed that it is in a worksheet change event.)

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value <> Cells(Target.Row, "B").Value Then
With Target
.AddComment
.Comment.Text Text:=Application.UserName & ":" & Chr(10)
.Comment.Visible = True
End With
End If

End Sub

 

[OssieMac]

I had an afterthought on this and wondered if you might appreciate a little
more information if you need to delete the comment or edit the info in an
existing comment. (You can't add a comment if one already exists.)

Private Sub Worksheet_Change(ByVal Target As Range)

With Target

If .Value <> Cells(Target.Row, "B").Value Then
If .Comment Is Nothing Then
.AddComment
End If

.Comment.Text Text:=Application.UserName & ":" & Chr(10)
.Comment.Visible = True
Else
If Not .Comment Is Nothing Then
.Comment.Delete
End If
End If

End With

End Sub

 

[dweber1188]

That is correct, any cell in a given row should compare its value to column B
of that row.

 

[dweber1188]

Thank you Rick.

This has got it just about where I want it. The only issue I am having now
is, even though it generates a comment, the comment box never appears, the
triangle just shows up in the corner of the cell but does not give the user a
chance to type anything.

I don't want the comments to always remain visible, that would just create
too much clutter. I am trying to get it to appear so the user can enter
comments and then disappear when they move to the next cell.

 

[OssieMac]

Try a combination of Worksheet_SelectionChange event and Worksheet_Change
event like the following.

It creates a comment if not already existing.
Displays the comment for editing when the cell is selected.
Hides the comment when the user moves off the cell.

Optional: Deletes the comment if target value equals column B value.

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Value <> Cells(Target.Row, "B").Value Then
If .Comment Is Nothing Then
.AddComment
End If
.Comment.Visible = True
.Comment.Text Text:= _
Application.UserName & ":" & Chr(10)
'********************************************
'Code between the asterisk lines is optional _
if you want to delete the comment when the _
target does equal the cell in column B.
Else
.Comment.Delete
'********************************************
End If
End With
End Sub

Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)

Application.DisplayCommentIndicator = _
xlCommentIndicatorOnly

If Not Target.Comment Is Nothing Then
Target.Comment.Visible = True
End If

End Sub

 

[OssieMac]

A modification to the code. I left out the test for a comment in the Else.
Can't delete a comment if it does not exist.

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Value <> Cells(Target.Row, "B").Value Then
If .Comment Is Nothing Then
.AddComment
End If
.Comment.Visible = True
.Comment.Text Text:= _
Application.UserName & ":" & Chr(10)
'********************************************
'Code between the asterisk lines is optional _
if you want to delete the comment when the _
target does equal the cell in column B.
Else
If Not .Comment Is Nothing Then
.Comment.Delete
End If
'********************************************
End If
End With
End Sub

Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)

Application.DisplayCommentIndicator = _
xlCommentIndicatorOnly

If Not Target.Comment Is Nothing Then
Target.Comment.Visible = True
End If

End Sub