2

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:

  1. Conditional Formatting on the sheet
  2. VBA code that executes when a change is made
  3. 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: enter image description here

Thanks in advance!

Tianna Wrona
  • 342
  • 2
  • 14
  • 2
    "Locked" cells have zero significance until the worksheet is protected. – Mathieu Guindon Aug 31 '17 at 18:28
  • 3
    Leave it protected, do `Worksheet.Unprotect`, run your code and `Worksheet.Protect` after that. – Moacir Aug 31 '17 at 18:29
  • @Moacir That worked, thank you. I also added in functionality so if the status of something goes "N/A", then changes back (shouldn't but could happen), then the changes reflect accordingly. I knew the answer was going to be something so simple. – Tianna Wrona Aug 31 '17 at 18:42
  • Glad to help. :) – Moacir Aug 31 '17 at 19:04

1 Answers1

0

Well, Community has been insisting (for days) that I look at this question, relentlessly pushing it to the top of my "relevant" queue, probably because it's tagged with my top 4 tags, and it doesn't technically have an Answer.

So, sorry Moacir, I'm swiping your "commented answer":

  1. Leave it protected,

  2. do Worksheet.Unprotect,

  3. run your code (and Worksheet.Protect) after that.

More Info:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105