VBA Conditional Formatting of Cells within limits

**seanfoxen** · 05-06-2013, 04:07 PM

I am trying to add conditional formatting to a group of cells with text. The formatting is based upon a formula that uses cell values(numbers) from elsewhere in the sheet. The formula is calculating whether a dynamic value is in or out of a specified range. When I input a value the number will change and if it is out of range then I want the cells to turn red, and if they are in then I want them to turn green. I have attached a sample of what I am looking. I am looking for both VBA and non VBA answers. I will end up needing this to be a macro.

Sean

**rylo** · 05-06-2013, 07:39 PM

Hi

Conditional Formatting:
Highlight the range E2:G2, Conditional Formatting, use a formula and enter the following
=AND($E2<=$F2,$E2>=$G2), Fill as Green
=OR($E2>$F2,$E2<$G2), Fill as Red

Worksheet Change Event

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row >= 2 And Target.Column >= 5 And Target.Column <= 7 Then
    If Cells(Target.Row, "E") > Cells(Target.Row, "F") Or Cells(Target.Row, "E") < Cells(Target.Row, "G") Then
      Cells(Target.Row, "E").Resize(1, 3).Interior.ColorIndex = 3
    Else
      Cells(Target.Row, "E").Resize(1, 3).Interior.ColorIndex = 4
    End If
  End If
End Sub

rylo