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...

 

[Fluff]

How about

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

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

 

[BettaJ]

Another issue arises... (sorry)

So when I type in MISC my E will be a look up, (so will be my F,G,& R)
The reason why I unlocked those FGR is b/c I wanted to manually type in.

BUT If i initially put in MISC for D and filled in the FFR manually...but decided to not put this in the MISC.
I delete MISC from D but whenever I put some value in D that is defined with a lookup for FGR, those fomulas disappear..
Anyway ONLY KEEP MISC to unlock and if it's NOT MISC it'll ALWAYS keep FGR with the cell's lookup formula still in tact??

does that make sense?

 

[Fluff]

That code does not change what's in the cells. If you have already overwritten the formulae, then you will need to put it back in.

 

[BettaJ]

Got it! Thank for your help Fluff.

 

[Fluff]

You're welcome & thanks for the feedback.
If you need help putting the formulae back in just shout