Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA code for locking cells based on conditional format colour

0

Hi,

I have a spreadsheet which allows users to mark there attendence 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 wwell so users cannot enter anything at all?

Any help much appreciated.

Thanks

excel
posted by: YP14 (rep: 5) on Mon May 18, 2020 at 10:44 am
Answer
Discuss

Answers

1
Selected Answer

This code works for anyone who may come across this post:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cl As Range

ActiveSheet.Unprotect

   For Each cl In Range("B8:GC95")

       If cl.DisplayFormat.Interior.ColorIndex = 16 Then

            cl.Locked = True

        Else

            cl.Locked = False

       End If

   Next cl

ActiveSheet.Protect

End Sub

posted by: YP14 (rep: 5) on Wed May 20, 2020 at 10:28 am
Discuss
0

Worksheet protection is designed to prevent users from making changes to the protected sheet. However, protection applies only to Locked cells. You can access the Locked property from the Protection tab of the Format Cells dialog.

This play between protecting and locking cells allows you to customize the access you grant. However, it may be hard to find a way to allow access to a cell at one time and deny it when the cell is gray. If this is your requirement you would have to employ VBA to change the Locked property of gray cells.

Of course, that hitches the cart before the horse. The cell's gray fill is the response to a mathematical calculation and the Locked property would be set to the same instruction. In fact, the Locked property ranks higher than the cell colour. Therefore the colour would probably indicate that the cell is locked. Therefore you would abandon CF in that case and set both the Locked property and the cell colour using VBA.

posted by: Variatus (rep: 4889) on Mon May 18, 2020 at 9:37 pm
Discuss

Discussion

Thank you for replying so quick. i think it would be a long task using vba to set the cell colour as the condition is not consistent (meaning some people will link to one person and others may not & also the attached file only consists a small sample, the list of people will be much longer). I have come up with the below code which i by logic should work, but it locks all cells rather than the grey, can you see where i've gone wrong?

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
Thanks
YP14 (rep: 5) May 19, '20 at 5:46 am
CF is harder to control than VBA, especially if you have complicated rules to implement. I don't like it because it's own management easily becomes fragmented and then the sheet will slow down. This can't happen with VBA.
Anyway, I found your code to work fine but I tested without protection. Consider setting the Protection on Workbook_Open and set it with UserInterfaceOnly = True. Then you don't have to worry about it again when carrying out changes in event procedures.
Perhaps, what you're up against is that your code doesn't unlock cells that don't qualify. Then cells stay locked and you think the code did it (it did, but not on that change). Try this instead, 
cl.Locked = (cl.DisplayFormat.Interior.Color = 8421504)
It will both lock and unlock.
Variatus (rep: 4889) May 19, '20 at 11:34 pm
I managed to get my code working, i was just missing an indent! Thanks for your repsonse.
YP14 (rep: 5) May 20, '20 at 10:26 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login