Hello,
I have created a payroll spreadsheet that only certain fields for corresponding days within a pay period need to be edited. There are certain days of the week (i.e.: Tuesday) that we have a product or products that are distributed that employees need to be paid on. I am trying to acheive the following:
=IF(WEEKDAY(D8)=3,do nothing,change cell to color -4105 and lock)
I initially had a formula written in the cell as such:
=IF(WEEKDAY(D8)=3,"1","")
...with a conditional formatting rule description of "format only cells with blanks, return color -4105"
...and a VBA code entered as the following:
Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Range("c9:r11")
If cell.Value = "" Then
ActiveSheet.Unprotect Password:="password"
cell.Locked = True
ActiveSheet.Protect Password:="password"
Else
ActiveSheet.Unprotect Password:="password"
cell.Locked = False
ActiveSheet.Protect Password:="password"
End If
Next
End Sub
The problem that I have encountered is that if I select the unlocked cell and then change the "1" to anything else, I have overwritten the formula. Even worse, if I accidently delete the "1" and hit enter, I am locked out of the cell due to my conditional formatting and VBA code. I have tried to create an Interior.Color clause within the VBA code but I haven't had any lock because what I am understanding is that VBA does not see color changes from conditional formatting. Any ideas? I have been searching forums for about a week now!
Bookmarks