我试图以循环跳过每一行的每行格式。它通过使用单元格AD8中的色标准标准格式化范围B8:Y8开始。下一个循环应使用AD10格式化B10:Y10,然后使用AD12等B12:Y12等。一直到第98行。这是将在循环内的格式代码:
Range("B8:Y8").Select
Selection.FormatConditions.AddColorScale ColorScaleType:=2
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueNumber
Selection.FormatConditions(1).ColorScaleCriteria(1).Value = 0
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValueNumber
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = "=$AD$8"
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0
End With
可以使B8:Y92的条件格式规则然后从奇数行中删除CFR。
可能是权宜的。Dim r As Long
With ActiveSheet
With .Range("B8:Y92")
.FormatConditions.Delete
.FormatConditions.AddColorScale ColorScaleType:=2
.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueNumber
.FormatConditions(1).ColorScaleCriteria(1).Value = 0
With .FormatConditions(1).ColorScaleCriteria(1).FormatColor
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValueNumber
.FormatConditions(1).ColorScaleCriteria(2).Value = "=$AD$8"
With .FormatConditions(1).ColorScaleCriteria(2).FormatColor
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0
End With
End With
For r = 9 To 91 Step 2
.Range("B" & r & ":Y" & r).FormatConditions.Delete
Next r
End With
请参阅如何避免在Excel VBA宏中使用选择。