How to conditional format based on the same cell changing value/override

Question

I'm wondering if it's possible to write code or a rule in conditional formatting where if I changed the value in a cell, that cell would highlight or change color to make it obvious that I changed that input criteria in a model.

I have a model where I certain assumptions are my inputs. I also have an override section where if they wanted to change the growth rate for example, that is possible simply by entering the cell. Is is possible to then have that cell change color after overriding the value in that cell?

Thanks!!

Answer 1

Use the WorksheetChangeEvent to detect when something has been changed:

Private Sub Worksheet_Change(ByVal Target As Range)
' do stuff
End Sub

Put the above code into the worksheet object (double click on the worksheet in the code editor).

Use Application.Intersect to narrow down what has been changed. For instance if you're only interested in cells A1 to A10:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
     ' do stuff
End If
End Sub

Then format your changed cell to taste:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then

     With Target.Interior
         .Pattern = xlSolid
         .PatternColorIndex = xlAutomatic
         .Color = 255
         .TintAndShade = 0
         .PatternTintAndShade = 0
     End With

End Sub

Target is the range for the affected cell. Of course the user can affect more than one cell at once e.g. by autofilling, so you may need to put some additional logic e.g if Target.Rows.Count > 1, but you get the idea.