VBA code not working in a Range

[GSY01]

Hi Guys,

I have the following code to add a comment each time a cell value changes, however if I change the Range to "N25" everything works perfectly, but if I want to have the code to cover all rows in column "N" I don't get any comment appear when the value changes.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Set Rng = Range("N:N")
On Error Resume Next
If Rng.Comment Is Nothing And Len(Rng.Value) > 0 Then
Rng.AddComment Now & "-" & Rng.Value
Else
Rng.Comment.Text vbNewLine & Now & "-" & Rng.Value, Len(Rng.Comment.Text) + 1
End If
Rng.Comment.Shape.TextFrame.AutoSize = True
End Sub

Thanks,

Gavin

 

[Norie]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range

    Set Rng = Range("N:N")

    If Intersect(Target, Rng) Is Nothing Then Exit Sub

    On Error Resume Next

    If Target.Comment Is Nothing And Len(Target.Value) > 0 Then 
        Target.AddComment Now & "-" & Target.Value
    Else
        Target.Comment.Text vbNewLine & Now & "-" & Target.Value, Len(Target.Comment.Text) + 1
    End If

    Target.Comment.Shape.TextFrame.AutoSize = True

End Sub

 

[GSY01]

Hi Norie,

Still no good.

 

[GSY01]

Hi Norie,

I apologize I should have mentioned earlier, I have the following formula in column "N" =INDEX(A$1:M$1,MATCH(TRUE,(T25:AF25<>0)*(COLUMN(T25:AF25)-COLUMN(T25)+1)>=D25,0))

If I use the code I have and only select one cell in "N", it works, but not when I select all on "N", if I type over the formula using my code I still don't get a comment.

When I use your code the only time it returns a comment is when I type over the formula.

is there away I can get the comment by leaving the formula in place.

Thanks

 

[Norie]

The change event is only triggered when a manual change is made on the worksheet.

 

[GSY01]

Excuse my ignorance as I am new to VBA, why does the code work if I only have the range as one cell i.e. "N25" I change the information in cell "D25" which changes the information in "N25" and inserts a comment.
As soon as I change the range to anything other than a single cell no comment is inserted.

 

[Norie]

Gavin

Your original code would be triggered by a manual change in any cell on the worksheet.

The code itself doesn't actually check, or do anything, with the cell/range that has been changed but it does look at/work with column N.

Why it only changes N25 I'm not sure, do any other cells in column N have comments?

 

[GSY01]

Hi Norie,

No Other comments in N.

I will work on a different outcome.

Thanks for your help.