如何选择具有条件格式范围的活动单元格和活动单元格列?

Question

我正在尝试使用基于我的日期范围("G2:Y2")的条件格式，我的日期变量在另一个工作表上。到目前为止，我可以得到日期范围("G2:Y2")改变颜色，但我不能选择单元格在我的activecell。column(第7至72行)进行相同的格式化。第4,5和6行必须保持不变。

Sub test()
Dim var1 As Variant 'Date 1
Dim var2 As Variant 'Date 2
Dim var3 As Variant 'Date 3
Dim var4 As Variant 'Date 4
Dim var5 As Variant 'Date 5
Dim var6 As Variant 'Date 6
Dim Z As Long
var1 = LEGENDE.Range("O2").Value
var2 = LEGENDE.Range("O3").Value
var3 = LEGENDE.Range("O4").Value
var4 = LEGENDE.Range("O5").Value
var5 = LEGENDE.Range("O6").Value
var6 = LEGENDE.Range("O7").Value
Z = ActiveCell.[Row]
For Each c In Range("G2:Y2")  'test range
c.Select
Z = ActiveCell.[Row]
If ActiveCell.Value >= var1 And ActiveCell.Value <= var2 Then
ActiveCell.Interior.color = 65535
'I would like to select the active cell and the activecell column with range 7 to 72 for conditional formatting in excel
Else
End If
Next
End Sub

Answer 1

一般避免使用Select和ActiveCell。参考c

For Each c In Range("G2:Y2")
If c.Value >= var1 And c.Value <= var2 Then
c.Interior.Color = vbYellow
Rows("7:72").Columns(c.Column).Interior.Color = vbYellow
End If
Next

注意vbYellow比65535更有意义。