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

 

[L. Howard]

This may help, where you use Worksheet_Calculate event.

excel - VBA code doesn't run when cell is changed by a formula - Stack Overflow

Regards,
Howard

 

[Michael M]

Change your event macro to a Worksheet_Calculate rather than worksheet_Change

 

[crazyeyeschase]

Change your event macro to a Worksheet_Calculate rather than worksheet_Change

I have tried that asll well and still nothing works.

Right now I have opened a new worksheet and all that is in it is two cells

A1 and A2
A1 is blank and A2 =A1+1

This is the code i am trying

Private Sub Worksheet_Calculate()
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
 Range("B3").Style = "insert"
End Sub

I have even tried removing the range however i get nothing at all. Macros are enabled and it is a macro enabled sheet i just dont understand.

I have tested the code that should change B3 to a new style and it works as its own macro just the Worksheet_Calculate doesn't seem to work.

 

[Michael M]

What happens if you insert a number into A1 and press enter
Sheet events need to have something happen in them to trigger the code !!

 

[Smitty]

Note that the Calculate event doesn't support the Target argument, so I'd simply call your VersionConv code from there instead.

 

[crazyeyeschase]

What happens if you insert a number into A1 and press enter
Sheet events need to have something happen in them to trigger the code !!

If i change the number in A1 then A2 changes but nothing else happens.

 

[crazyeyeschase]

If i change the number in A1 then A2 changes but nothing else happens.

Scratch that now that i closed the doc and reopend it its now working. And as mentioned had a debug error for the target.

 

[crazyeyeschase]

So the question now is how can i get it to run when only Cell A2 is changed? Other wise this would be running all the time.

 

[Smitty]

It will run all the time, but you're only evaluating one cell so it shouldn't be too bad.