Lock cells based on conditional format colour

[YV14]

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

 

[YV14]

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