Conditional formating based on sheet protection status

C

chrisgarcia78

New Member
Joined
Jun 15, 2024
Messages
31
Office Version
  1. 2021
Platform
  1. Windows
Hi there,

Is there a way to automatically format a cell color based on the sheet protection status? if the sheet is unprotected to change cell A1 background color to RED and Green if protected.

Thank you.
 
Sort by date Sort by votes
OK, let's try putting it in both the Sheet Activate, Selection, and Change events, to try to capture most possibilities.
In order for the following code to work, it absolutely MUST be placed in the "ThisWorkbook" module in VBA. If you put it in any other model, it will not work.
Also note that I assumed that your are NOT using a password in your protection (since you did not mention that earlier when I asked):
VBA Code: 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

'   Check protection status of sheet
    If Sh.ProtectContents = True Then
'       Change background color of cell A1 to red
        Sh.Unprotect
        Sh.Range("A1").Interior.Color = vbGreen
        Sh.Protect
    Else
'       Change background color of cell A1 to red
        Sh.Range("A1").Interior.Color = vbRed
    End If

End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'   Check protection status of sheet
    If Sh.ProtectContents = True Then
'       Change background color of cell A1 to red
        Sh.Unprotect
        Sh.Range("A1").Interior.Color = vbGreen
        Sh.Protect
    Else
'       Change background color of cell A1 to red
        Sh.Range("A1").Interior.Color = vbRed
    End If
    
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

'   Check protection status of sheet
    If Sh.ProtectContents = True Then
'       Change background color of cell A1 to red
        Sh.Unprotect
        Sh.Range("A1").Interior.Color = vbGreen
        Sh.Protect
    Else
'       Change background color of cell A1 to red
        Sh.Range("A1").Interior.Color = vbRed
    End If
    
End Sub
 
Upvote 0
Solution

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Joe4 said:
OK, let's try putting it in both the Sheet Activate, Selection, and Change events, to try to capture most possibilities.
In order for the following code to work, it absolutely MUST be placed in the "ThisWorkbook" module in VBA. If you put it in any other model, it will not work.
Also note that I assumed that your are NOT using a password in your protection (since you did not mention that earlier when I asked):
VBA Code: 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

'   Check protection status of sheet
    If Sh.ProtectContents = True Then
'       Change background color of cell A1 to red
        Sh.Unprotect
        Sh.Range("A1").Interior.Color = vbGreen
        Sh.Protect
    Else
'       Change background color of cell A1 to red
        Sh.Range("A1").Interior.Color = vbRed
    End If

End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'   Check protection status of sheet
    If Sh.ProtectContents = True Then
'       Change background color of cell A1 to red
        Sh.Unprotect
        Sh.Range("A1").Interior.Color = vbGreen
        Sh.Protect
    Else
'       Change background color of cell A1 to red
        Sh.Range("A1").Interior.Color = vbRed
    End If
   
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

'   Check protection status of sheet
    If Sh.ProtectContents = True Then
'       Change background color of cell A1 to red
        Sh.Unprotect
        Sh.Range("A1").Interior.Color = vbGreen
        Sh.Protect
    Else
'       Change background color of cell A1 to red
        Sh.Range("A1").Interior.Color = vbRed
    End If
   
End Sub
Click to expand...
This code works just like you said if added in "ThisWorkbook", is it possible to have this in each individual sheet so I can use a different target for each sheet instead of A1 for all sheets?
 
Upvote 0
You would then need to use the Activate, Change, and SelectionChange events on each individual worksheet.

So, you would need to put code like this in each of the Sheet modules, and adjust the range as you see fit:
VBA Code: 
Private Sub Worksheet_Activate()

'   Check protection status of sheet
    If ActiveSheet.ProtectContents = True Then
'       Change background color of cell A1 to red
        ActiveSheet.Unprotect
        ActiveSheet.Range("A1").Interior.Color = vbGreen
        ActiveSheet.Protect
    Else
'       Change background color of cell A1 to red
        ActiveSheet.Range("A1").Interior.Color = vbRed
    End If
    
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

'   Check protection status of sheet
    If ActiveSheet.ProtectContents = True Then
'       Change background color of cell A1 to red
        ActiveSheet.Unprotect
        ActiveSheet.Range("A1").Interior.Color = vbGreen
        ActiveSheet.Protect
    Else
'       Change background color of cell A1 to red
        ActiveSheet.Range("A1").Interior.Color = vbRed
    End If
    
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   Check protection status of sheet
    If ActiveSheet.ProtectContents = True Then
'       Change background color of cell A1 to red
        ActiveSheet.Unprotect
        ActiveSheet.Range("A1").Interior.Color = vbGreen
        ActiveSheet.Protect
    Else
'       Change background color of cell A1 to red
        ActiveSheet.Range("A1").Interior.Color = vbRed
    End If
    
End Sub
 
Upvote 0
Joe4 said:
You would then need to use the Activate, Change, and SelectionChange events on each individual worksheet.

So, you would need to put code like this in each of the Sheet modules, and adjust the range as you see fit:
VBA Code: 
Private Sub Worksheet_Activate()

'   Check protection status of sheet
    If ActiveSheet.ProtectContents = True Then
'       Change background color of cell A1 to red
        ActiveSheet.Unprotect
        ActiveSheet.Range("A1").Interior.Color = vbGreen
        ActiveSheet.Protect
    Else
'       Change background color of cell A1 to red
        ActiveSheet.Range("A1").Interior.Color = vbRed
    End If
   
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

'   Check protection status of sheet
    If ActiveSheet.ProtectContents = True Then
'       Change background color of cell A1 to red
        ActiveSheet.Unprotect
        ActiveSheet.Range("A1").Interior.Color = vbGreen
        ActiveSheet.Protect
    Else
'       Change background color of cell A1 to red
        ActiveSheet.Range("A1").Interior.Color = vbRed
    End If
   
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   Check protection status of sheet
    If ActiveSheet.ProtectContents = True Then
'       Change background color of cell A1 to red
        ActiveSheet.Unprotect
        ActiveSheet.Range("A1").Interior.Color = vbGreen
        ActiveSheet.Protect
    Else
'       Change background color of cell A1 to red
        ActiveSheet.Range("A1").Interior.Color = vbRed
    End If
   
End Sub
Click to expand...
Wow this is great, I appreciate your help so much! Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
How to control formatting of unprotected cells in a protected sheet? Ideas?
Replies
2
Views
326
Dman333
D
C
  • Solved
Protecting all cells in a sheet based on .TintAndShade
Replies
4
Views
78
Peter_SSs
Peter_SSs
A
  • Question Question
VBA help - Locking & unlocking on certain times of day
Replies
4
Views
99
Anthony86
A
D
  • Question Question
Unable to lock the cell based on current date
Replies
1
Views
208
AC PORTA VIA
A
M
  • Question Question
Using VBA code in a protected excel sheet
Replies
2
Views
468
MagicCarpet
M

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

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
Back
Top