loop through rows

K

Kevin Carter

hi
i have a worksheet that contains all the days of the year on each row
in the column i have employee names
i am creating a vacation tracker making sure that i have enought employees on shift
i have counted the number of blank cells in a range(s8)
if the value is different from 8 i want to lock the range of cells
leaving the cell with a value in it unlocked

i want to do this to stop more than one employee having vaction same day
but allowing the employee who has a vacation to change his/her mind

i have the following code that locks a range of cell


How can i adapt this to
1. loop through the rows
2 leave the cell with a value in it unlocked


Private Sub Worksheet_Change(ByVal Target As Range)
If [s8] <> 7 Then
ActiveSheet.Unprotect
Range("e8:k8").Locked = True
ActiveSheet.Protect
'Remove locked property if B3's value is anything else or is deleted.
Else
ActiveSheet.Unprotect
Range("e8:k8").Locked = False
'Optional, reprotect sheet
ActiveSheet.Protect
End If
End Sub

thanks

kevin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Autofit Row Height Macros 2
Changing Cells depending on data entered in other cells 1
Conditional Formatting Using Min/Max 10
Locking specific cells 1
Excel locking cell based on value in another cell 1
Excel Locking cells based on value in another cell 2
vba input box with restriction on what can be entered. 2
Using Drop Down Menu for Macro 6

Top