How do I conditionally format multiple cells based off of one cell's value in VBA? RRS feed

  • Question

  • I have a range of cells that loop through and are conditionally formatted based on data at the end of the table. Green is low, yellow is in between, and red is high. Right now, the top left cell in a group is conditionally formatted based on its value (its centered across selection but the 3 is in the top left cell technically), but I'd like the surrounding cells ( 2 to the right, 1 down, and 1 down + 2 to the right) to change color based on that top left cell's value as well. I've tried varying versions of offset and indirect, but I can't quite grasp the right one. Here is the code:

     For j = 1 To 10
            lLow = Cells(i, 4).Offset(, 43).Value
            lhigh = Cells(i, 4).Offset(, 44).Value
        Cells(i, 4).Offset(, 43).Value = "1"
        Cells(i, 4).Offset(, 44).Value = "2"
    Range(Cells(i, j * 4 + 2), Cells(i + 1, j * 4 + 4)).FormatConditions.Delete
    With Cells(i, j * 4 + 2).FormatConditions.Add(Type:=xlCellValue,_
        _Operator:=xlGreater, Formula1:="=" & lhigh)
          .Interior.Color = rgbRed
     End With

    Here's what works for 2 cells over, but it isn't very dynamic or efficient as I'd have to type this for each surrounding cell:     

     Cells(i, j * 4 + 4).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
     Selection.FormatConditions   (Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Interior
         .Color = 255
      End With
      Next j
    Wednesday, July 6, 2016 6:52 PM


  • Please do as follows:

    - Make a new file
    - Open the VBA editor
    - Add a regular module
    - Copy the code below into that module
    - Execute the macro
    - Switch to the sheet
    - Write 1 into D6
    - Write 2 into D6
    - Manage the rules and have a look which conditions are added
    - Compare them with the code, read the comments in the code


    Sub Test()
      Dim Middle As Range, Where As Range
      Dim LowFormula As String, HighFormula As String
      Dim FC As FormatCondition
      'This are the formulas for the condition
      'Note: A1 is just a place holder and is replaced later with the address of the Middle cell
      LowFormula = "=A1<=1"
      HighFormula = "=A1>=2"
      'Refer to the middle cell
      Set Middle = Range("D6")
      'Now resize to surround the cells around (2 horizontal, 1 vertical, each side)
      Set Where = Middle.Offset(-1, -2).Resize(3, 5)
      'Where refers to B5:F7 now
      'In that cells
      With Where.FormatConditions
        'Remove all conditions
        'Add one for the lower value, refer to the middle cell
        Set FC = .Add(xlExpression, , Replace(LowFormula, "A1", Middle.Address))
        FC.Interior.Color = vbGreen
        'Add one for the high value, refer to the middle cell
        Set FC = .Add(xlExpression, , Replace(HighFormula, "A1", Middle.Address))
        FC.Interior.Color = vbRed
        'You can add more if you like same way as shown above...
      End With
    End Sub

    Wednesday, July 6, 2016 7:42 PM