RSprengers
Board Regular
- Joined
- Nov 9, 2005
- Messages
- 54
I have the following (test)code in a workbook (no module)
What I'm trying to do is this:
I have 5 cells that are automatically updating (C3:G3) through DDE connection. These five cells have a comment (each one is different)
Cell A3 contains the Min. value from C3:G3 (=Min(C3:G3))
Now I want the comment in A3 to be the same as the comment in the cell that it refers to (The cell with the minimum value)
It works the first time I run the macro, but if I start changing the data (either manually or by DDE) A3 updates the minimum value, but not the corresponding comment.
What am I doing wrong?
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not (Intersect(Target, Range("A3")) Is Nothing) Then
Call test
End If
Application.EnableEvents = True
End Sub
Sub test()
Dim newcomment As String
For Each cell In Range("c3:g3")
If cell.Value = Range("a3").Value Then
newcomment = cell.Comment.Text
Range("A3").ClearComments
Range("A3").AddComment (newcomment)
End If
Next cell
End Sub
What I'm trying to do is this:
I have 5 cells that are automatically updating (C3:G3) through DDE connection. These five cells have a comment (each one is different)
Cell A3 contains the Min. value from C3:G3 (=Min(C3:G3))
Now I want the comment in A3 to be the same as the comment in the cell that it refers to (The cell with the minimum value)
It works the first time I run the macro, but if I start changing the data (either manually or by DDE) A3 updates the minimum value, but not the corresponding comment.
What am I doing wrong?
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not (Intersect(Target, Range("A3")) Is Nothing) Then
Call test
End If
Application.EnableEvents = True
End Sub
Sub test()
Dim newcomment As String
For Each cell In Range("c3:g3")
If cell.Value = Range("a3").Value Then
newcomment = cell.Comment.Text
Range("A3").ClearComments
Range("A3").AddComment (newcomment)
End If
Next cell
End Sub