Limit Macro to Non-Contiguous Range of Cells

[jski21]

Good day Team,

This macro works fine except I need to isolate it to a non-contiguous range of cells (commented out I the code


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Inserts a comment box if the cell value is less than 99%

'=$B$26:$B$28,$B$30:$B$32,$B$34:$B$36,$B$38:$B$40,$E$26:$E$28,$E$30:$E$32, _
'$E$34:$E$36,$E$38:$E$40,$J$26:$J$28,$J$30:$J$32,$J$34:$J$36,$J$38:$J$40, _
'$M$26:$M$28,$M$30:$M$32,$M$34:$M$36,$M$38:$M$40

If Sh.Name = "2018 standards" Or Sh.Name = "2017 standards" Then Exit Sub
On Error GoTo errorout 'Resume Next
If Target <= 0.989 Then
With Target
.AddComment
.Comment.Text Text:="Explanation:" & Chr(10)
.Comment.Visible = True
End With
End If
errorout:
Err.Clear
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "2018 standards" Or Sh.Name = "2017 standards" Then Exit Sub

Dim MyShp As Comment, _
CommentList As Comments

Set CommentList = Sh.Comments
For Each MyShp In CommentList
MyShp.Visible = False
Next MyShp

End Sub



How to? Thanks.

 

[DanteAmor]

Try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Inserts a comment box if the cell value is less than 99%


    Set Rng = Range("$B$26:$B$28,$B$30:$B$32,$B$34:$B$36,$B$38:$B$40,$E$26:$E$28,$E$30:$E$32," & _
        "$E$34:$E$36,$E$38:$E$40,$J$26:$J$28,$J$30:$J$32,$J$34:$J$36,$J$38:$J$40," & _
        "$M$26:$M$28,$M$30:$M$32,$M$34:$M$36,$M$38:$M$40")
    If Not Intersect(Target, Rng) Is Nothing Then
        If Sh.Name = "2018 standards" Or Sh.Name = "2017 standards" Then Exit Sub
        On Error GoTo errorout 'Resume Next
        If Target <= 0.989 Then
            With Target
                .AddComment
                .Comment.Text Text:="Explanation:" & Chr(10)
                .Comment.Visible = True
            End With
        End If
    End If
errorout:
    Err.Clear
End Sub

 

[jski21]

Yes! Thanks!

 

[DanteAmor]

You're welcome!