Highlight cells within a defined range

[rjplante]

I have a worksheet change macro that will highlight col A when changed and then the column in the same row that has a YES in row 4. I also have code to change any other cell when changed individually. I would like to restrict the highlight of the changed cells only for specific columns and rows. I do not want any cells in column B to get highlighted, and only cells changed after row 5 (from 6-506). I only want to have the following columns defined as the target columns (C, E, G, I, K, M, and O) or column numbers (3,5,7,9,11,13,and 15).

How do I modify the code to prevent any highlighting in column B and cells in rows 1-5?

Private Sub Worksheet_Change(ByVal Target As Range)

    '   Color custom row and delete lookup formula
    Dim MyRow As Long
    Dim MyCol As Long
    Dim col As Variant
  
    Application.EnableEvents = False
    MyRow = Target.Row
    Target.Offset(0, 2).Calculate
  
    '   Check to see if there is an "#N/A" error in column C for the active row
    If IsError(Range("C" & MyRow)) Then
        ActiveSheet.Unprotect Password:="123"
  
    '   Highlight the current row cells in column A and C yellow.
        Range("A" & MyRow).Interior.Color = RGB(255, 255, 0)
        For Each col In Array(4, 6, 8, 10, 12, 14, 16) 'columnnumbers for the columns with YES
            If UCase(Cells(4, col)) = "YES" Then Cells(MyRow, col - 1).Interior.Color = RGB(255, 255, 0)
        Next
  
    '   Clear out column Q text
       Range("Q" & MyRow).ClearContents
  
    '   Expand row height to fit
        Rows(ActiveCell.Row).AutoFit
      
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFiltering:=True, Password:="123"

    Else
    
        ActiveSheet.Unprotect Password:="123"
        MyCol = Target.Column
        Cells(MyRow, MyCol).Interior.Color = RGB(255, 255, 0)
    
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFiltering:=True, Password:="123"
            
    End If
    
    Application.EnableEvents = True

End Sub

 

[Zot]

All I can see is code under ELSE is coloring the column B. Try skip column be like

Else
    
        ActiveSheet.Unprotect Password:="123"
        MyCol = Target.Column
        If MyCol = 2 Then
            Select Case MyRow
                Case 1, 2, 3, 4, 5
                
                Case Else
                    Cells(MyRow, MyCol).Interior.Color = RGB(255, 255, 0)
            End Select
        End If
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFiltering:=True, Password:="123"
            
    End If

Hope this works

 

[rjplante]

Thank you for the code update. I just had to make one change:

If MyCol = 2 Then

changed to

If MyCol > 2 Then

This allowed me to make changes to column B and no highlight will occur. The rest of the columns will highlight as desired. I really appreciate the help.

 

[Zot]

Thanks for update