Highlight cells when a cell changes via formula

Question

I'd like to create a macro on Excel to highlight a range of cells when the date in a particular cell changes. The cell draws the date information from Bloomberg (BDP formula), and the workbook refreshes daily.

I've tried this solution:

Private Sub Worksheet_Change(ByVal Target As Range)

(https://www.thespreadsheetguru.com/blog/trigger-your-vba-macros-to-run-based-on-specific-cell-value-change).

However, it only works when the formula in the cell changes, and not when the cell automatically updates the date information when refreshed.

Is there a simple solution to my problem?

try conditional formatting: support.office.com/en-ie/article/… — cyboashu

A.S.H A.S.H · Accepted Answer · 2017-01-18T05:30:19

If what you want to detect is just one cell, the solution is perhaps easy. Put a Worksheet_Calculate event in the worksheet that contains this cell:

Private Sub Worksheet_Calculate()
     Static oldVal As Variant
     If Me.Range("A1").Value <> oldVal Then
        Me.Range("A2:C4").Interior.ColorIndex = 6
    End If
    oldVal = Me.Range("A1").Value
End Sub

This supposes you want to detect the change in cell A1 and the range you want to highlight is A2:C4. Adjust these ranges to your need.

Highlight cells when a cell changes via formula

1 Answers