Test1 in the code below creates a conditional formatting rule for cells H2:H13 to fill the cell red when the cell value is >= 1. This is working and I get the expected results: H2:H6 have no fill and H7:H13 have the red colour fill.
Test1 also creates a conditional formatting rule for cells G2:G13 to fill the cell red when the adjacent H cell value is >=1. This is done by adding a conditional format cell by cell, however this creates 12 rules for G2:G13. I would prefer 1 rule for G2:G13, if possible.
Sub Test1()
'Test data
With Sheet1
.Range("G1").Value = "G"
.Range("G2").FormulaR1C1 = "10"
.Range("G2:G13").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=5, Trend:=False
.Range("H1").Value = "H"
.Range("H2").FormulaR1C1 = "0.5"
.Range("H2:H13").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=0.1, Trend:=False
End With
Dim r As Integer: r = 13
Dim cell As Range
With Sheet1
.Cells.FormatConditions.Delete
'Conditional formatting of H2:H13 based on cell value >= 1
With .Range("H2:H" & r)
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=1"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With
'Conditional formatting of G2:G13 based on adjacent H cell value >= 1
For Each cell In .Range("G2:G" & r)
cell.FormatConditions.Add Type:=xlExpression, Formula1:="=" & cell.Offset(0, 1).Address & ">=1"
cell.FormatConditions(cell.FormatConditions.Count).SetFirstPriority
With cell.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
cell.FormatConditions(1).StopIfTrue = False
Next
End With
End Sub
Display More
My question is: what is the VBA code for creating a single conditional formatting rule for a range of cells based on a range of adjacent cells? For this example code, one rule for G2:G13.
Test2 below is one attempt to create a single rule, but the conditional formatting on G2:G13 isn't giving the desired result.
Sub Test2()
'Test data
With Sheet1
.Range("G1").Value = "G"
.Range("G2").FormulaR1C1 = "10"
.Range("G2:G13").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=5, Trend:=False
.Range("H1").Value = "H"
.Range("H2").FormulaR1C1 = "0.5"
.Range("H2:H13").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=0.1, Trend:=False
End With
Dim r As Integer: r = 13
With Sheet1
.Cells.FormatConditions.Delete
'Conditional formatting of H2:H13 based on cell value >= 1
With .Range("H2:H" & r)
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=1"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With
'Conditional formatting of G2:G13 based on adjacent H cell value >= 1
With .Range("G2:G" & r)
.FormatConditions.Add Type:=xlExpression, Formula1:="=$H$2:$H$" & r & ">=1"
'.FormatConditions.Add Type:=xlExpression, Formula1:="=" & .Offset(0, 1).Address(False, False) & ">=1"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With
End With
End Sub
Display More
Thanks for any help.