Worksheet_Change when the value of a formula changes

[tomleitch]

Hi all,

I have a code that runs when cells are changed in my spreadsheet

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("B10:H5010")) Is Nothing Then Target.Interior.Color = rgbYellow
End Sub

However some of the cells are formula cells and these don't get their colour changed when the value returned by the formula changes (e.g. when a date is recalculated).


Is there any way around this?

Thanks
Tom

 

[JoeMo]

Try using the worksheet_calculate event rather than the worksheet_change event.

 

[tomleitch]

Hi JoeMo, I can't figure out how to get this worksheet calculate event to work with my code.

I have a range of cells that I want to see if any of the calculations change in that particular cell and then change the colour only of that cell.


Regards
Tom

 

[JoeMo]

Sounds like you need to focus on worksheet_change events for the precedent cells of the formulaic cells you wish to monitor. Precedent cell(s) would be the one(s) that hold constants (not formulas) and the user would change manually. For example, suppose A1 holds the formula: =IF(B1="","",B1). If a user enters or changes text or a number in B1, cell A1 will change, but the change event you want to monitor is the one for B1. In the change_event code you can then change the fill color of A1 whenever the content of B1 is changed.

 

[tomleitch]

Yep, got it sorted - thanks for the pointer