VBA code not working in a Range

GSY01

New Member
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

Well-known Member
Try this.
Code:
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

New Member
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

Well-known Member
The change event is only triggered when a manual change is made on the worksheet.
 

GSY01

New Member
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

Well-known Member
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?
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top