为固定范围的单元格设置背景颜色
我在Excel电子表格中有VBA代码。它用于根据该单元格中的值设置单元格的字体和背景颜色。我使用VBA而不是“条件格式”,因为我有3个以上的条件。代码是:为固定范围的单元格设置背景颜色
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
Set d = Intersect(Range("A:K"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
If c >= Date And c <= Date + 5 Then
fc = 2: fb = True: bc = 3
Else
Select Case c
Case "ABC"
fc = 2: fb = True: bc = 5
Case 1, 3, 5, 7
fc = 2: fb = True: bc = 1
Case "D", "E", "F"
fc = 2: fb = True: bc = 10
Case "1/1/2009"
fc = 2: fb = True: bc = 45
Case "Long string"
fc = 3: fb = True: bc = 1
Case Else
fc = 1: fb = False: bc = xlNone
End Select
End If
c.Font.ColorIndex = fc
c.Font.Bold = fb
c.Interior.ColorIndex = bc
c.Range("A1:D1").Interior.ColorIndex = bc
Next
End Sub
问题出在“c.Range”行。它总是使用当前单元格作为“A”,然后向右移动四个单元格。我希望它在当前行的“真实”单元格“A”中开始,并转到当前行的“真实”单元格“D”。基本上,我想要一个固定的范围,而不是一个动态的范围。
所以c.Range("A1:D1")
有它自己的相对范围。
一种解决方法是使用工作表的范围属性。
我向顶部添加了两行(#added
),并在底部更改了一行(#changed
)。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
Dim ws As Worksheet ''#added
Set d = Intersect(Range("A:K"), Target).Cells
Set ws = d.Worksheet ''#added
If d Is Nothing Then Exit Sub
For Each c In d.Cells
If c >= Date And c <= Date + 5 Then
fc = 2: bf = True: bc = 3
Else
Select Case c.Value
Case "ABC"
fc = 2: bf = True: bc = 5
Case 1, 3, 5, 7
fc = 2: bf = True: bc = 1
Case "D", "E", "F"
fc = 2: bf = True: bc = 10
Case "1/1/2009"
fc = 2: bf = True: bc = 45
Case "Long string"
fc = 3: bf = True: bc = 1
Case Else
fc = 1: bf = False: bc = xlNone
End Select
End If
c.Font.ColorIndex = fc
c.Font.Bold = bf
c.Interior.ColorIndex = bc
ws.Cells(c.Row, 1).Interior.ColorIndex = bc ''#changed
ws.Cells(c.Row, 2).Interior.ColorIndex = bc ''#added
ws.Cells(c.Row, 3).Interior.ColorIndex = bc ''#added
ws.Cells(c.Row, 4).Interior.ColorIndex = bc ''#added
Next
End Sub
但是,这设定“第一”行(A 1:D 1)的A至D.我想从“当前”行的A到D.如果我在单元格E7中输入“5/1/2010”,我希望A7到D7更改。如果我在单元格c99中输入“5/1/2010”,我想从A99到D99进行更改。基本上,当前列的前四个单元格。 – 2010-04-30 18:36:27
@Count:很棒。修复了这个问题。让我知道事情的后续。 – bernie 2010-04-30 19:06:58
既然您告诉我“c.Row”是当前行的编号,我将最后四行合并为一个: ws.Range(“A”&c.Row&“:D”&c.Row) .Interior.ColorIndex = bc 非常感谢。 – 2010-04-30 19:27:25
只是为了验证,我假设你担心允许的条件数量,因为这将被交付给不仅仅是xl2007的用户? – guitarthrower 2010-04-30 19:30:37
我们使用的Excel 2003似乎只允许3个条件。用户有6个条件可以测试,包括他们无法在向导中工作的日期范围。 – 2010-04-30 19:53:41