Run macro when Cell updates by formula

[crazyeyeschase]

Alright before i came here i did quite a bit of google searching.

I have a macro that changes the style of specific cells dependent on the version which is in another cell.
my macro

Sub VersionConv()
    Worksheets("Sheet4").Activate
Sheets("All Items").Unprotect "******"
If Range("A8").Value = "1.0" Then
    Range("F456").Style = "data"
    Range("F659").Style = "insert"
    Else
    If Range("A8").Value = "2.0" Then
        Range("F456").Style = "insert"
        Range("F659").Style = "data"
    End If
End If
Sheets("All Items").Protect "******"
End Sub

The cells with the "data" style and protected and cannot be changed while the cells with the "insert" style can be changed. In the newer version two things have changes and need to be flipped, however some users may continue using the old version so it need to be easy to switch back and fourth.

This macro works fine however i need it so when the value in "A8" changes this macro will run. I have tried many different things and none seem to work at all.

<code>
Private Sub Worksheet_Change(ByVal Target As Range)
     Dim KeyCells As Range
     Set KeyCells = Range("A8")
      If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
         MsgBox "Cell " & Target.Address & " has changed."
     End If
End Sub
</code>

<code>Private Sub Worksheet_Change(ByVal Target As Range)
     If Intersect(Target, Me.Range("A8")) Is Nothing Then Exit Sub
     Application.EnableEvents = False 'to prevent endless loop
      MsgBox "You changed THE CELL!"
      Application.EnableEvents = True 
End Sub</code>

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$A$1" Then
    MsgBox "You changed the cell"
   End If
End Sub

The value in "A8" is dependent on a second workbook

A8=[Workbook2.xlsm]Sheet4!$G$4

I have tried changint the value by changing the cell in workbook2 and nothing
I have tried clicking (A8) and nothing
I have tried changing the value in (A8) and nothing

I was trying to figure this out without MrExcel's help however I have tried at least 10 different macros and nothing is working and my frustration level is growing .

And yes before you ask the macro is in the same worksheet the cell (A8) is in.

Their are many other macros in this worksheet mostly buttons and one that finds the highest value in a range.

I have also tried to replace the MsgBox in the macros to either
call VersionConv
or i put the VersionConv macro into its own modual and use
Application.Run "VersionConv.VersionConv"

and still nothing Please please help me out here..

 

[crazyeyeschase]

Ok now i am getting errors with the ranges however they work fine in a modual

Private Sub Worksheet_Calculate()
Dim psw As String
MsgBox "woot"

Pwd = "*****"
    If Range("A8").Value = "1.0" Then
        ActiveSheet.Unprotect Password:=Pwd
        [COLOR=#ff0000]Range("F456").Style = "data"
        Range("F659").Style = "insert"[/COLOR]
        ActiveSheet.Protect Password:=Pwd
        Else
        If Range("A8").Value = "2.0" Then
            ActiveSheet.Unprotect Password:=Pwd
          [COLOR=#ff0000]  Range("F456").Style = "insert"
            Range("F659").Style = "data"[/COLOR]
            ActiveSheet.Protect Password:=Pwd
        End If
    End If
End Sub

The red areas are whats causing a debug message.

It only does this when changing the value in a separate workbook.

 

[crazyeyeschase]

Alright got her figured out.

Private Sub Worksheet_Calculate()
Dim psw As String
Dim wkb As Workbook
Dim sht As Worksheet

Set wkb = Workbooks("All Items.xlsm")
Set sht = wkb.Sheets("All Items")
sht.Activate

Pwd = "*****"
    If Range("A8").Value = "1.0" Then
        ActiveSheet.Unprotect Password:=Pwd
        Range("F456").Style = "data"
        Range("F659").Style = "insert"
        ActiveSheet.Protect Password:=Pwd
        Else
        If Range("A8").Value = "2.0" Then
            ActiveSheet.Unprotect Password:=Pwd
            Range("F456").Style = "insert"
            Range("F659").Style = "data"
            ActiveSheet.Protect Password:=Pwd
        End If
    End If
End Sub