Hello all,
I can't seem to find this answer on the forum so I'm going to post this to see if anyone can help me.
If it's already in the forum somewhere, if you woudn't mind leading me to it.
So I have 1 VBA(?) that allows me to unlock certain cells when another is filled.
Then that one can then unlock another cell based on certain ranges of cells being fille.
Think of it as a check list for chores..1 cell is checked off, then another..and until all chores are complete, the person gets some money as the last unlocked cell is the guy that checks it for approval.
Anyway, I have another VBA(?) that when you double click the unlocked cell can be filled a green color. And left click once and it goes back to no fill.
I can't put them on the same sheet as below--it allows me to do one but then the color won't work:
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("E4:E39")) Is Nothing Then
'Changed something in col E
Cells(Target.Row, "F").Locked = Not (Target <> "")
End If
If Not Intersect(Target, Range("F4:F39")) Is Nothing Then
Range(Cells(Target.Row, "J"), Cells(Target.Row, "J")).Locked = _
(WorksheetFunction.CountBlank(Range(Cells(Target.Row, "A"), Cells(Target.Row, "G"))) <> 0)
End If
Me.Protect
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 4
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 0
End Sub
Would appreciate any help.
*Unfortunately, I cannot upload the file or get a screenshot as I don't think I can redact any info on in.
Thank you.
I can't seem to find this answer on the forum so I'm going to post this to see if anyone can help me.
If it's already in the forum somewhere, if you woudn't mind leading me to it.
So I have 1 VBA(?) that allows me to unlock certain cells when another is filled.
Then that one can then unlock another cell based on certain ranges of cells being fille.
Think of it as a check list for chores..1 cell is checked off, then another..and until all chores are complete, the person gets some money as the last unlocked cell is the guy that checks it for approval.
Anyway, I have another VBA(?) that when you double click the unlocked cell can be filled a green color. And left click once and it goes back to no fill.
I can't put them on the same sheet as below--it allows me to do one but then the color won't work:
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("E4:E39")) Is Nothing Then
'Changed something in col E
Cells(Target.Row, "F").Locked = Not (Target <> "")
End If
If Not Intersect(Target, Range("F4:F39")) Is Nothing Then
Range(Cells(Target.Row, "J"), Cells(Target.Row, "J")).Locked = _
(WorksheetFunction.CountBlank(Range(Cells(Target.Row, "A"), Cells(Target.Row, "G"))) <> 0)
End If
Me.Protect
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 4
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 0
End Sub
Would appreciate any help.
*Unfortunately, I cannot upload the file or get a screenshot as I don't think I can redact any info on in.
Thank you.
Last edited: