0

I have a code that changes a cell value when a certain range is changed. When I manually change a value in that range the code works just fine. But when a value in that range is changed by a formula the cell which needs to be changed is not updating. Is it possible to let excel see when a formula changes cell value, so my code that changes a cell value works.

This is my code:

   Dim KeyCells As Range

' The variable KeyCells contains the cells that will change by user
    Set KeyCells = Workbooks(Projectplanner).Sheets("Planning").Range("B9:C13,10:10")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

'Set value of cell E4 to 1 when values in KeyCells has been changed
            Workbooks(Projectplanner).Sheets("Planning").Range("A7").Value = 1

EDIT after replies: 

Private Sub Worksheet_Calculate()

'|  Define current projectplanner
    Dim Projectnumber As String
        Projectnumber = ActiveWorkbook.Sheets("Planning").Range("A6").Value2
    
    Dim Projectplanner As String
        Projectplanner = Projectnumber & ".xlsm"

    If Range("Employe1").Value <> PrevVal1 Then
        Workbooks(Projectplanner).Sheets("Planning").Range("Employe1").Offset(0, 1).Value = 1
        PrevVal1 = Range("Employe1").Value
    End If
    
    If Range("Employe2").Value <> PrevVal2 Then
        Workbooks(Projectplanner).Sheets("Planning").Range("Employe2").Offset(0, 1).Value = 1
        PrevVa2 = Range("Employe2").Value
    End If
    
    If Range("Employe3").Value <> PrevVal3 Then
        Workbooks(Projectplanner).Sheets("Planning").Range("Employe3").Offset(0, 1).Value = 1
        PrevVal3 = Range("Employe3").Value
    End If

and so on and so on for 19 ranges.

Jelle van der Heijden
  • 47
  • 5
  • 1
    You need to use a different event, the [Worksheet_Calculate](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.calculate(even)) event. – BigBen Nov 15 '19 at 14:40
  • https://stackoverflow.com/questions/11406628/vba-code-doesnt-run-when-cell-is-changed-by-a-formula – Siddharth Rout Nov 15 '19 at 15:30
  • Thanks, @SiddharthRout Your comment on that topic seems to work until I add more than one range. instead of one range like your example I made 19 ranges like this, and now excel keeps crashing. See my post for what kind of code i used. – Jelle van der Heijden Nov 19 '19 at 09:42

0 Answers0