» Set background color depending on value verification using VBA in Microsoft Excel

Question:
For entries or changes of formula values, a proof with the prior values is to be initiated. If the new cell value is higher, the background color shall change to


Answer:
Place the code below into the standard module

Private Sub Worksheet_Calculate()

   Dim rng As Range, rngAct As Range

   Set rng = Me.UsedRange

   With Worksheets("Dummy")

      For Each rngAct In rng.Cells

         If rngAct.Value < .Range(rngAct.Address).Value Then

            rngAct.Interior.ColorIndex = 6

         ElseIf rngAct.Value > .Range(rngAct.Address).Value Then

            rngAct.Interior.ColorIndex = 3

         End If

      Next rngAct

      .Range(rng.Address).Value = rng.Value

   End With

End Sub



Sub OutOfOn()

   With Worksheets("Dummy")

      If .Visible = True Then .Visible = xlVeryHidden Else .Visible = True

   End With

End Sub