Change color of cell when formula result changes

[lara223]

I have a large database that I need to be able to see when formula results change. I have the below code that will pop up a message box when there is a change. However, its not convenient for 50+ message boxes to pop up every time the power query is refreshed. Can someone help me modify the code so that the cell simply changes color instead?

Private Sub Worksheet_Calculate()
    Dim rng As Range, cell As Range
    Dim CellHasChanged As Boolean
   
    Set rng = Me.Range("J3:J2000")
    For Each cell In rng.Cells
        With cell
           CellHasChanged = CBool(.ID <> .Value)
           If CellHasChanged Then
            .ID = .Value
            MsgBox "Cell " & cell.Parent.Name & cell.Address & " has changed.", vbInformation, "Cell Value Changed"
            Exit For
           End If
        End With
    Next cell
    Set rng = Nothing
End Sub

Thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Calculate()
    Dim rng As Range, cell As Range
    Dim CellHasChanged As Boolean
   
    Set rng = Me.Range("J3:J2000")
    For Each cell In rng.Cells
        With cell
           CellHasChanged = CBool(.ID <> .Value)
           If CellHasChanged Then
               .ID = .Value
               .Interior.Color = vbRed
           Else
               .Interior.Color = xlNone
           End If
        End With
    Next cell
    Set rng = Nothing
End Sub

 

[lara223]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Calculate()
    Dim rng As Range, cell As Range
    Dim CellHasChanged As Boolean
  
    Set rng = Me.Range("J3:J2000")
    For Each cell In rng.Cells
        With cell
           CellHasChanged = CBool(.ID <> .Value)
           If CellHasChanged Then
               .ID = .Value
               .Interior.Color = vbRed
           Else
               .Interior.Color = xlNone
           End If
        End With
    Next cell
    Set rng = Nothing
End Sub

The code is able to run through when I changed to this but the cell did not change to red as I would have expected.

 

[Fluff]

Do J3:J2000 contain formulae?

 

[dmt32]

Hi,
I went to respond to your first post about my original code but then saw it deleted

@Fluff has provided same answer I was about to provide.

The code is able to run through when I changed to this but the cell did not change to red as I would have expected.

if your values are numeric try corecing the ID string to numeric

CellHasChanged = CBool(Val(.ID) <> .Value)

BTW - welcome to forum


Dave

 

[lara223]

Do J3:J2000 contain formulae?

Yes they do.