Automatically insert and\or change comments based on vlookup of cell value

Hi Everyone,

I am after a code that will insert a comment into a cell based on a vlookup of the cell value.

I have three columns (B, I and P) with formulas using data from other columns (C:H, J:O and Q:V respectively) in the same sheet. I want a comment automatically inserted in each cell in columms B, I and P based on a vlookup of the cell value using defined name ranges. Eg, looking at the attached pdf Test Sheet.pdf, B4 comment would be "Rank 4 Description" by using vlookup(B4,"Tier_One",2,0), I4 comment would be "Rank 2 Description" by using vlookup(I2,"Tier-Two",2,0), etc.

The data used for the formulas will change often, therefore, the comments will also need to be updated as and when the cell value changes.

I have been trying to modify the below code (I didn't write this code) to suit but have no real idea of what I am doing:-

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = ("B4") Then
        s = Application.VLookup(Range("B4:B200"), Range("Tier_One"), 2, 0)
        With Range("B4", Range("B200").End(xlUp))
            .ClearComments
            .AddComment
            .Comment.Visible = False
            .Comment.Text Text:=s
        End With
    End If
End Sub

Any help will be greatly appreciated.

Thanx