Lock cells based on conditional format colour

YV14

New Member
Joined
May 19, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Hi,

I have a spreadsheet which allows users to mark there attendance in the office on a particular day. However, there is a condition where if a certain person is coming in to the office, people sat next to them cannot come in same day and this applies multiple times. I have done conditional formatting so that the cells grey out for people who cannot come in when one of the person is attending, however is it possible to lock the grey cells as well so users cannot enter anything at all?

I have done the below code but this seems to lock a cell when it is grey and 'Attending' is picked from the drop down. Can anyone help?

Alternately, if there is a another way to do it i would be more than happy to do that as long as the grey cells are locked.

Thanks
 

Attachments

  • Attendance.PNG
    Attendance.PNG
    7.1 KB · Views: 11

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Forgot to put my code!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cl As Range


ActiveSheet.Unprotect

For Each cl In Target

If cl.DisplayFormat.Interior.Color = 8421504 Then

cl.Locked = True


End If

Next cl

ActiveSheet.Protect

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,992
Messages
6,175,829
Members
452,673
Latest member
LaMiaAvy

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