inserting comments to cells based on the values of that cell and one other cell (VBA)

Hi all,

I wish to set up a condition on cell whereby a comment box appears automatically if the user enters a certain value into that cell, BUT another cell (D12) is left blank.

If the 'conditional' cell is blank (or equal to zero), and the cell itself (D29) is not equal to zero, then I want a comment appears to say "error".

The code I have tried using is shown below. FYI it works with one criteria ie no "And Range("D29:AP29")", but i need both criteria to be accounted for in order for the condition to work.

Private Sub Worksheet_Calculate()
   Dim rCell As Range

   Set rCell = Range("D29:AP29")
   On Error Resume Next
   rCell.Comment.Delete
   On Error GoTo 0
   If Range("D12:AP12") <> 0 And Range("D29:AP29") = 0 Then rCell.AddComment ("error")
End Sub

Any help would very much appreciated!!

Thanks,

Michael

You cannot apply this to an entire range.
I haven't tested but you would have to do it for each cell individually.

Hi and welcome to the forum. Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(Since this is your first post I'll change it for you but please note for the future)

Wouldn't a conditional format which painted cells red where an error condition occurred be better than VB code?

Try this

Private Sub Worksheet_Calculate()
Dim rCell   As Range
Dim cCell   As Range
Dim rng     As Range
Set rCell = Range("D29:AP29")
Set cCell = Range("D21:AP21")
On Error Resume Next
rCell.Comment.Delete
Err.Clear
On Error GoTo 0
If WorksheetFunction.CountIfs(cCell, 0) <> cCell.Columns.Count Then
    For Each rng In cCell
        If rng.Value <> 0 And rng.Offset(8, 0).Value = 0 Then rng.Offset(8, 0).AddComment Text:="error"
    Next rng
End If
End Sub

Try using this Worksheet_Change event procedure

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell As Range, r1 As Range, r2 As Range

    If Not Intersect(Target, Range("D29:AP29")) Is Nothing Then
        Set r1 = Range("D12:AP12")
        Set r2 = Range("D29:AP29")
        r2.ClearComments

        For Each rCell In r2
            If rCell <> 0 Or r1.Cells(1, rCell.Column - 3) <> 0 Then rCell.AddComment ("error")
        Next rCell
    End If
End Sub

You said value = 0 then you have to edit and check on blank value instead of 0
So I assume that you actually wnat to test on the value 0 and not on the fact if the cell is empty or not. Correct and thorough information is everything

Private Sub Worksheet_Calculate()
Dim rCell   As Range
Dim cCell   As Range
Dim rng     As Range
Set rCell = Range("D29:AP29")
Set cCell = Range("D21:AP21")
On Error Resume Next
rCell.Comment.Delete
Err.Clear
On Error GoTo 0
If WorksheetFunction.CountIfs(cCell, "") <> cCell.Columns.Count Then
    For Each rng In cCell
        If rng.Value <> "" And rng.Offset(8, 0).Value = "" Then rng.Offset(8, 0).AddComment Text:="error"
    Next rng
End If
End Sub

I don't know if the countifs works correct this way but this would be the main idea