Highlight cells within a defined range

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
503
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?

VBA Code:
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
All I can see is code under ELSE is coloring the column B. Try skip column be like

VBA Code:
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
 
Solution

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
503
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
Thanks for update
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top