wuxuan_0
wuxuan_0
2019-03-17 12:35

VBA 条件格式中的地址怎么设为变量?

图片说明
我要根据Q列中的内容变化,实现相对应单元格所在行某一区域变更颜色。Q列中的单元格有的是合并后的。如下面代码中$Q$6想设为变化的。
Private Sub CommandButton1_Click()
Dim Sht1 As Worksheet
Dim i, e, k, b
Dim j As Range
Set Sht1 = ActiveWorkbook.Sheets("10-玖瑞-4种")
b = Sht1.Range("IV5").End(xlToLeft).Column
For i = 6 To Sht1.Range("H" & Rows.Count).End(xlUp).Row
Set j = Range("Q" & i).MergeArea
If Range("Q" & i).Address <> j.Address And Range("Q" & i).Address = j.Item(1).Address Then
e = j.Rows.Count
k = i + e - 1
Else
k = i
End If
With Range(Sht1.Cells(i, 23), Sht1.Cells(k, b))
.Interior.Pattern = xlNone
.FormatConditions.Delete
.FormatConditions.Add(xlExpression, , "= OR(**$Q$6**=""关闭"",$Q$6="""")").Interior.Color = 5287936
.FormatConditions.Add(xlExpression, , "= $Q$6=""延续""").Interior.Color = 255
.FormatConditions.Add(xlExpression, , "= $Q$6=""保留""").Interior.Color = 65535
.FormatConditions.Add(xlExpression, , "= $Q$6=""新增""").Interior.Color = 10734587
End With
Next i
End Sub


```单元格可能为合并
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

相关推荐