help automating manual code

[tadd9]

Hi guys, i've been trying for 2 days now to automate some manual code i have, what i mean by this is...

I have this code which i have written...

 Private Sub Worksheet_Change(ByVal Target As Range)


Dim StartSh As Worksheet
Set StartSh = ActiveSheet


* On Error Resume Next
* If Target.Value = "i" And Target.Column = 17 Then
*** ActiveSheet.Unprotect
*** Range("T" & Target.Row).Formula = ""
*** Range("R" & Target.Row).Formula = "=P" & Target.Row & ""
*** Range("T" & Target.Row).Locked = False
*** Range("R" & Target.Row).Locked = True
*** ActiveSheet.Protect
* End If
* On Error Resume Next
* If Target.Value = "e" And Target.Column = 17 Then
*** ActiveSheet.Unprotect
*** Range("T" & Target.Row).Formula = "=IF(F" & Target.Row & "="""","""",IF(F" & Target.Row & "=0,0,ROUND(((F" & Target.Row & ")*($T$3)+$T$4),2)))"
*** Range("R" & Target.Row).Formula = ""
*** Range("T" & Target.Row).Locked = True
*** Range("R" & Target.Row).Locked = False
*** ActiveSheet.Protect
* End If
* On Error Resume Next
* If Target.Value = "ei" And Target.Column = 17 Then
*** ActiveSheet.Unprotect
*** Range("T" & Target.Row).Formula = ""
*** Range("R" & Target.Row).Formula = ""
*** Range("T" & Target.Row).Locked = False
*** Range("R" & Target.Row).Locked = False
*** ActiveSheet.Protect
* End If
* On Error Resume Next
* If Target.Value = "" And Target.Column = 17 Then
*** ActiveSheet.Unprotect
*** Range("R" & Target.Row).Formula = "=P" & Target.Row & ""
*** Range("T" & Target.Row).Formula = "=IF(F" & Target.Row & "="""","""",IF(F" & Target.Row & "=0,0,ROUND(((F" & Target.Row & ")*($T$3)+$T$4),2)))"
*** Range("R" & Target.Row).Locked = True
*** Range("T" & Target.Row).Locked = True
*** ActiveSheet.Protect
* End If
* On Error Resume Next
End Sub

(Not sure why my copy paste of the code added ** but they are spaces in my code not *s, sorry)

Which does its job perfectly (even though it may be very messy and more complicated than needs be)(not sure if i need all the on error resume next lines though, they are there because when i altered other information on the sheet or deleted other information i got an error (type 13 mismatch irc))

So the issue im facing is that with this above code i appear to have to manually change the value of column 17 to e, i, ei, or blank for this vba to trigger...

I would like to have it so that this vba will trigger (unlocking and locking cells and removing or entering formulas accordingly) if a formula in column 17 is changed OR if i manually change it...

I copy and paste many rows and columns of information at a time which changes many formula values and in turn changes the formula in column 17 to show the value of e, i, ei or blank...

I also delete rows and columns of information and would need it to revert back to the state it was in (meaning do the command that is associated with a "" in column 17...

I would also like to be able to manually override and enter my own letter or blank in column 17 and have the correct command run...

I do appologise for just dumping my excel issues on you all, if anyone could be of assistance that would be great...

I have previously tried this code...

(Again sorry for the ***s they should be spaces)

 Sub Worksheet_Change(ByVal Target As Range)
*
*** Dim WatchRange As Range
*** Dim IntersectRange As Range
*
*** Set WatchRange = Range("Q7:Q1006")
*** Set IntersectRange = Intersect(Target, WatchRange)
*
*** On Error GoTo err_chk
*** If Not (IntersectRange Is Nothing) And (Target.Value = "e") Then
******* ActiveSheet.Unprotect
******* Range("T" & Target.Row).Formula = "=IF(F" & Target.Row & "="""","""",IF(F" & Target.Row & "=0,0,ROUND(((F" & Target.Row & ")*($T$3)+$T$4),2)))"
******* Range("R" & Target.Row).Formula = ""
******* Range("T" & Target.Row).Locked = True
******* Range("R" & Target.Row).Locked = False
******* ActiveSheet.Protect
*** End If
*** If Not (IntersectRange Is Nothing) And (Target.Value = "i") Then
******* ActiveSheet.Unprotect
******* Range("T" & Target.Row).Formula = ""
******* Range("R" & Target.Row).Formula = "=P" & Target.Row & ""
******* Range("T" & Target.Row).Locked = False
******* Range("R" & Target.Row).Locked = True
******* ActiveSheet.Protect
*** End If
*** If Not (IntersectRange Is Nothing) And (Target.Value = "ei") Then
******* ActiveSheet.Unprotect
******* Range("T" & Target.Row).Formula = ""
******* Range("R" & Target.Row).Formula = ""
******* Range("T" & Target.Row).Locked = False
******* Range("R" & Target.Row).Locked = False
******* ActiveSheet.Protect
*** End If
*** If Not (IntersectRange Is Nothing) And (Target.Value = "") Then
******* ActiveSheet.Unprotect
******* Range("R" & Target.Row).Formula = "=P" & Target.Row & ""
******* Range("T" & Target.Row).Formula = "=IF(F" & Target.Row & "="""","""",IF(F" & Target.Row & "=0,0,ROUND(((F" & Target.Row & ")*($T$3)+$T$4),2)))"
******* Range("R" & Target.Row).Locked = True
******* Range("T" & Target.Row).Locked = True
******* ActiveSheet.Protect
*** End If
*** On Error GoTo 0
*
*** Exit Sub
*
'** Error Handling
err_chk:
*** If Err.Number = 13 Then
******* Err.Clear
******* Exit Sub
*** Else
******* MsgBox Err.Number & vbCrLf & Err.Description
*** End If
*
End Sub

And many other lines of code, learning as i go, to get this to work but as i said for 2 days now ive failed miserably

(i should mention that i am a bit of a noob to excel and have only been using it for a few weeks... and gotten myself in way over my head, doing things i didnt even realise it could)

Today being my birthday ive downed tools and admitted defeat... for today at least... but if one of the great wizards of excelforum could help me to see where i am going wrong it would be greatly appreciated...

As i said my first bit of code works great and changed range T and R exactly as i need when i manually enter the information...

Unfortunately when i then put a formula in column Q (column 17 in the code) the vba wouldnt trigger...

Since then i have had endless loops, multiple errors, excel crashes and all sorts of wonderfully annoying issues...

My code attempt above caused an loop and stack error and other code just didnt trigger...

I should mention i will require this code to be monitoring column 17 (Q) for any changes i make or the formula value shows and changing the same row column T and R according to whats in Q...

Thank anyone from the bottom of my heart if they can assist me here, it would be a great birthday present :D

Thanks for reading guys.
Regards