none
HIGHLIGHTING ACTIVE ROW for PROTECTED SHEETS RRS feed

  • Question

  • I have an EXCEL 2016 sheet, and I need, when an EXCEL user is at any cell in this sheet, the entire row becomes highlighted. I have 2 problems.

    First, I need to add the restriction that says: highlighting be "DEactivated" for Cells "S3 to AB8"? NOTE: I want the restriction to apply ONLY to these cells not the entire rows that these cells are located in?

    Second, I will need to Protect my "EXCEL sheet" which locks and hides certain columns. I need the VBA code to work under both "protected" and "UNprotected" modes. My current code does not allow this.

    Here is my current code:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        If Target.Cells.Count > 2 Then Exit Sub
        If Target.Row <> 1 And Target.Row <> 2 Then
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            Range(Sh.Cells(3, 1), Sh.UsedRange.SpecialCells(xlLastCell)) _
                .EntireRow.Interior.ColorIndex = xlColorIndexNone
            Target.EntireRow.Interior.ColorIndex = 19
            Application.ScreenUpdating = True
            Application.EnableEvents = True
        End If
    End Sub
    Sunday, October 16, 2016 6:37 PM

All replies

  • I have an EXCEL 2016 sheet, and I need, when an EXCEL user is at any cell in this sheet, the entire row becomes highlighted. I have 2 problems:

    First, I need to add the restriction that says: highlighting be "DEactivated" for Cells "S3 to AB8"? NOTE: I want the restriction to apply ONLY to these cells not the entire rows that these cells are located in?

    Second, I will need to Protect my "EXCEL sheet" which locks and hides certain columns. I need the VBA code to work under both "protected" and "UNprotected" modes.

    Here is my current code:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
        If Target.Row <> 1 And Target.Row <> 2 Then
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            Range(Sh.Cells(3, 1), Sh.UsedRange.SpecialCells(xlLastCell)) _
                .EntireRow.Interior.ColorIndex = xlColorIndexNone
            Target.EntireRow.Interior.ColorIndex = 8
            Application.ScreenUpdating = True
            Application.EnableEvents = True
        End If
    End Sub
    Sunday, October 16, 2016 11:43 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, October 17, 2016 3:15 AM
  • Hi Rkbot,

    >> First, I need to add the restriction that says: highlighting be "DEactivated" for Cells "S3 to AB8"?

    What do you mean by this? Have your current code achieve this? If it has, for applying this code under protected and unprotected sheets, you could try below code which is used to unprotected and protected worksheet.

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        If Target.Cells.Count > 2 Then Exit Sub
        If Target.Row <> 1 And Target.Row <> 2 Then
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            If ActiveSheet.ProtectContents = True Then
            ActiveSheet.Unprotect 123
            Range(Sh.Cells(3, 1), Sh.UsedRange.SpecialCells(xlLastCell)) _
                .EntireRow.Interior.ColorIndex = xlColorIndexNone
            Target.EntireRow.Interior.ColorIndex = 19
            ActiveSheet.Protect 123
            Else
            Range(Sh.Cells(3, 1), Sh.UsedRange.SpecialCells(xlLastCell)) _
                .EntireRow.Interior.ColorIndex = xlColorIndexNone
            Target.EntireRow.Interior.ColorIndex = 19
            End If
    
            Application.ScreenUpdating = True
            Application.EnableEvents = True
        End If
    End Sub

    If you first issue has not been resolved, I would suggest you try above code first to check whether the second issue has been resolved. If we have resolved the second issue, I would suggest you post a new thread for the first and share us more information about your first issue. I think you could share us a screen shot about your different situation.

    Best Regards,

    Edward

           

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, October 17, 2016 6:14 AM