Conditional Comments

[tjbusch]

All,

I want to add comments to each cell of a worksheet, but they will change depending on the contents of each corresponding cell from another worksheet. Since I'm checking with more than one other worksheet, it will need the ability to append multiple comments into one cell comment field. Here is the logic I'm thinking about:

For all cells in worksheet4.range A1:Z20
If worksheet1.samecell = "A" then amend cellcomment = "A for worksheet1"
If worksheet2.samecell = "B" then amend cellcomment = "B for worksheet2"
If worksheet3.samecell = "C" then amend cellcomment = "C for worksheet3"

Thanks,

Tim

[Rick_Stanich]

You can start with this post, it has the code to append data in a comment for the "activecell".
http://www.excelforum.com/excel-prog...-comments.html

[tjbusch]

Thanks for that. That solves the appending question. However, I'm a newbie to VBA macros, so need more detail on the overall picture, i.e. how does it plug the comments for the entire range of the worksheet, and how does it reference the other worksheets?

Sorry if this is too basic.

[MickG]

Hi, Try this:-
Run from sheet (4)

 Dim sht As Worksheet, rng As Range, cl As Range

For Each sht In ActiveWorkbook.Worksheets
    Set rng = sht.Range("A1:Z20")

    If sht.Index < 4 Then
        For Each cl In rng
             If Sheets("sheet4").Range(cl.Address).NoteText <> "" Then
                 Sheets("sheet4").Range(cl.Address).NoteText _
                    Text:=Sheets("sheet4").Range(cl.Address).NoteText _
                        & Chr(10) & cl.Value & "  for Worksheet " & sht.Index
            Else
                Sheets("sheet4").Range(cl.Address).NoteText _
                    Text:=cl.Value & " for Worksheet " & sht.Index
            End If
         Next cl
    End If
Next sht

Regards Mick

[tjbusch]

Ok guys, here's where I am so far. I think I'm close, but I'm now getting an error 424 Object Required. What am I missing?

Thanks for your help!

Sub Add_Comments()

Set rng = Worksheets("test4").Range("A1:Z20")

For Each c1 In rng
If IsEmpty(Worksheets("test1").Range(cl.Address).Value) Then
   Worksheets("test4").Range(c1.Address).NoteText Text:=Worksheets("test4").Range(c1.Address).NoteText _
      & Worksheets("test1").Range(c1.Address).Value & " for GS -"
   End If
Next c1


For Each c1 In rng
If IsEmpty(Worksheets("test2").Range(cl.Address).Value) Then
   Worksheets("test4").Range(c1.Address).NoteText Text:=Worksheets("test4").Range(c1.Address).NoteText _
      & Worksheets("test2").Range(c1.Address).Value & " for CS - "
   End If
Next c1

For Each c1 In rng
If IsEmpty(Worksheets("test3").Range(cl.Address).Value) Then
   Worksheets("test4").Range(c1.Address).NoteText Text:=Worksheets("test4").Range(c1.Address).NoteText _
      & Worksheets("test3").Range(c1.Address).Value & " for RCS - "
End If
Next c1

End Sub

[tjbusch]

Ignoring for a moment that I forgot to add a Not in the If statement so I would actually ADD the comment, the 424 error seems to be occuring on the IF statement. Your thoughts?
Thank you!

[MickG]

Hi, Try this:-
You seem to have your "1's" mixed up with your "L's" i.e "Cl" not "C1"
NB:- If you use the Ch(10), you will put each comments line on a New Line , really depends what you want.

Dim rng As Range, cl As Range, Num As Integer, sht As String

Set rng = Worksheets("test4").Range("A1:Z20")

For Num = 1 To 3
sht = "test" & Num
    For Each cl In rng
        If Not IsEmpty(Worksheets(sht).Range(cl.Address).Value) Then
            Worksheets("test4").Range(cl.Address).NoteText Text:=Worksheets("test4").Range(cl.Address).NoteText _
                & Worksheets(sht).Range(cl.Address).Value & " for CS - "
        End If
    Next cl
Next Num

Regards Mick

[tjbusch]

Of course....it's the simple stuff that bites you. Thanks for the help gang!