I am using a tracker for testing new changes, and when. If a new change is N/A, I don't want to delete it, I want to disable (and turn gray) all the cells that are available to select the date it was completed. But only in that row. I've tried using the following methods but haven't had luck with any:
- Conditional Formatting on the sheet
- VBA code that executes when a change is made
- Data Validation (think this is possible but am unfamiliar with how this really works)
Here is the code I have:
Private Sub worksheet_change(ByVal Target As Range)
Dim keycells As Range
Set keycells = Range("G:G")
lastcol = CInt(Sheet1.Cells(1,Sheet1.Columns.Count).End(xlToLeft).Column)
If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then
r = Range(Target.Address).Row
MsgBox "There was a change"
If Range(Target.Address).Value = "N/A" Then
MsgBox "we got this far"
Range("H" & r & ":" & Cells(r, lastcol).Address).Locked = True
End If
End If
End Sub
Both message boxes show, but the cells don't lock. So I tried unlocking all cells, then protecting the sheet. Then I set something to "N/A" and get the error "Unable to set the Locked property of the Range Class".
Here is an idea of what my data looks like:
Thanks in advance!