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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.