Excel VBA - 单元格颜色更改
问题描述:
我试图编写一个宏,它根据列L中的值更改颜色。如果L列中的单元格为YES,则Hightlight列B单元格为红色。但是,下面的宏不起作用或失败。它运行但什么都不做。Excel VBA - 单元格颜色更改
Sub ColorMeElmo()
Dim i As Long, r1 As Range, r2 As Range
For i = 2 To 5
Set r1 = Range("L" & i)
Set r2 = Range("B" & i & ":B" & i)
If r1.Value = "YES" Then r2.Interior.Color = vbRed
Next i
End Sub
答
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CCell As Range
Dim sht As Worksheet
Set CCell = Range("L:L")
Set sht = Worksheets("SheetName")
If Target.Count = 1 Then
If Not Application.Intersect(CCell, Range(Target.Address)) _
Is Nothing Then
If Target.Value = "YES" Then
sht.Cells(Target.Row, 2).Interior.Color = RGB(255, 0, 0)
End If
End If
End If
End Sub
答
把它放在你想要观看的图表中。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CCell As Range
Dim sht As Worksheet
Set CCell = Range("L:L")
Set sht = Worksheets("Sheet1") 'EDIT
If Target.Count = 1 Then
If Not Application.Intersect(CCell, Range(Target.Address)) _
Is Nothing Then
If Target.Value = "YES" Then
sht.Cells(Target.Row, 2).Interior.Color = RGB(255, 0, 0)
End If
End If
End If
End Sub
为什么不使用条件格式? –
适合我。你在L2:L5范围内的任何地方都有“YES”吗? (注意:不只是“是”,实际上是“是”) – YowE3K
HI Scott,我会提供给用户,我认为宏将更容易运行。 – user7675421