adding a hover comment to a cell populated by text from another cell

[chriswrcg]

Is there a way to have two worksheets in a workbook and have a cell in one worksheet display a comment based on the content of a cell from the other worksheet when you hover you mouse on top of the cell?

cell a1 displays yes as text on worksheet 1.
cell b1 displays is the sky blue as text but when you hover your mouse on top of it yes is displayed as a comment.

[davesexcel]

That is called a ScreenTip, and you can make it reference a any range with VBA
This will show whatever is in sheet2A1 in the screen tip.

Sub Button1_Click()
    Dim sh As Worksheet, ws As Worksheet
    Set sh = Sheets("Sheet1")
    Set ws = Sheets("Sheet2")

    With sh

        .Range("A3").Hyperlinks.Add Anchor:=.Range("A3"), Address:="", SubAddress:= _
                                    "Sheet2!A1", ScreenTip:=ws.Range("A1").Value, TextToDisplay:="Howdy!"
    End With
End Sub

[chriswrcg]

this really isn't what I am needing. it is almost like a v lookup because not every cell will show the same thing.
cell b1 on worksheet 2 will display what is in the cell a1 from worksheet 1
cell b2 on worksheet 2 will display what is in the cell a2 from worksheet 2

but instead of just looking for and then copying the data from one sheet to the next it should not be shown unless the mouse hovers over the cell.

the reason I am trying to do this is because of conditional formatting. I am using a v lookup to populate the cells on sheet 2 with either a 1 or a 2 from sheet 1 and then display a green cell if 1 and a red cell if 2. I need to also be able to hover over or click on the cell and show a persons name based on a cell from worksheet 1

[davesexcel]

AH, I was going to work on comments for you at first, then you said the text was blue, so thought you were referring to hyperlinks.

Supply a sample workbook, you can use Vlookup or find in vba and put the results in a comment.

[chriswrcg]

here you go. the green squares on the log worksheet should say the names from the data sheet when you hover over them.

[davesexcel]

I think i have it, let me know,

Sub Add_Comments()
    Dim rng As Range, c As Range, s
    Dim sh As Worksheet
    Set sh = Sheets("Full Tracker Log")
    With sh
    Set rng = .Range("C6:AG" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    For Each c In rng.Cells
        s = Application.VLookup(c.Offset(, 31), Sheets("Data").Range("A1:D100"), 4, False)
        If Not IsError(s) Then
            With c
                .ClearComments
                .AddComment
                With .Comment
                    .Visible = False
                    .Text Text:=s
                    .Shape.Height = 15
                End With
            End With
        End If
    Next c
    End With
End Sub

[chriswrcg]

where do I put it?

[davesexcel]

It's VBA code, put it in a module like you have in your previous posts, then run it.