Unlock Cell from Another cells value

[BettaJ]

I'm appologize if this topic has been covered...I just couldn't find the right one that would work.

I have an excel, where Column D is a value used in a look up for column E,F,G, and R.
So when I put in 12345 it will automatically fill in the lookup values and fill in the E,F,G, and R. (duh, I know)
One value for D is "MISC".
When I put this one in, the values for E,F,G, and R are blank as I have to put those in manually.

So in order to keep my excel from being edited or [formulas] seen, I protected the sheet.
And for this, I wanted a VBA that would allow me to UNLOCK cells E,F,G, and R when the value of D is "MISC".
*Keep in mind cells E,F,G, and R all have a lookup formula in them as they connect with D's value.

For testing purposes, I just tried only column F and R as it seems the VBA function of RANGE only allows Cell1 and Cell2 so my fomula is my Sheet Code is as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect ""

If Not Intersect(Target, Range("D3:D100")) = "MISC" Then
Range(Cells(Target.Row, "F"), Cells(Target.Row, "R")).Locked = False

End If
Me.Protect ""
End Sub

I have no clue how to do this as I believe it unlocks that whole row...even the G column that wasn't in the VBA.
Moreover, I get some error code when I fill in values for random cells [on that line] stating some ERROR CODE 91.

I would appreciate any help...

 

[mrshl9898]

Might be a better idea to fix those formulas or add an IF?

=IF(D3="MISC","MISC",yourlookupformula)

Why is MISC being missed when other lookups are working? What's the formula?

 

[BettaJ]

Mrshl9898, thanks for the response...
Are you suggesting the formula you wrote should be in the VBA?
I'm trying to UNLOCK certain cells [after the sheet has been protected] when D# shows "MISC".
*My lookup is fine as it works within my excel. I'm just trying to do something when i use MISC, where I can actually manually TYPE something in the cells I want to unlock.

The VBA that I have now works:
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect ""

If Intersect(Target, Range("D3:D200")) = "MISC" Then
Range(Cells(Target.Row, "F"), Cells(Target.Row, "R")).Locked = Not (Target = "MISC")
End If
Me.Protect ""
End Sub

BUT, now if I put a date in my column/row A#, then an error of 91 appears...
I don't get it...

 

[BettaJ]

IF I put anything in on any cell that error comes up

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect ""

If Not Intersect(Target, Range("D3:D200")) Is Nothing Then
   If Target.Value = "MISC" Then Target.Offset(, 2).Resize(, 13).Locked = False
End If
Me.Protect ""
End Sub

 

[BettaJ]

Fluff Thanks for the response.

The function helped to eliminate the ERROR 91 when typing things in other cells, BUT it seems to have unlocked a locked cell on the right of the D column. (Column M, which I didn't want unlocked; only R)

I have columns A-T
E,F,G,M, and R are locked.
So, when I type in MISC in the D column, I wanted it to unlock ONLY F,G, and R
*I only mentioned F and R because I saw that a VBA Range can only have Cell1 and Cell 2 and not a third so I figured if I can at least get the F & R unlocked..

Thank!

 

[Fluff]

Ok, how about

   If Target.Value = "MISC" Then Intersect(Target.EntireRow, Range("F:G,R:R")).Locked = False

 

[BettaJ]

Fluff,

PERFECT! IT THINK IT WORKED THANKS!

 

[BettaJ]

Problem...

The formula works great, but if I decided to delete MISC from D, those previous FGR are still unlocked...
Is there a way to lock it back up when MISC is not there

Thankx

 

[BettaJ]

figured it out i think..

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect ""

If Not Intersect(Target, Range("D3:D200")) Is Nothing Then
If Target.Value = "MISC" Then Intersect(Target.EntireRow, Range("F:G,R:R")).Locked = False
End If

If Not Intersect(Target, Range("D3:D200")) Is Nothing Then
If Target.Value <> "MISC" Then Intersect(Target.EntireRow, Range("F:G,R:R")).Locked = True
End If

Me.Protect ""
End Sub