如果放置了值，则使用Excel-VBA为范围y着色；如果放置了值，则使用范围x着色 [英] Use Excel-VBA to colour a range y if value is certain number is placed AND colour range x if value is certain number is placed

问题描述

我需要在Excel VBA(2016)中编写条件格式，而无需使用现有的条件格式设置工具.由于我是新手，并且尝试了以下一段时间，因此请您帮助我.

I need to program a conditional format in Excel VBA (2016) without using the existing conditional formatting tool. As I am a newbie and tried for a while the following, I'm asking you to help me.

我想写这个在一个私有子目录中:用于范围E18:G18和K1:K10:

I want to write this e.g. in a private sub: for range E18:G18 and K1:K10:

如果值> = 1，则颜色=绿色

If value is >=1 then colour = green

如果值是< 1或&"；然后变成红色

If value is <1 or "" then colour red

对于范围B1:B10

for range B1:B10

如果值> = 3，则颜色=绿色

If value is >=3 then colour = green

如果值是< 3&> 0，然后颜色为黄色

If value is <3 & >0 then colour yellow

如果值是0或"，红色

我的代码如下-当我保存它时，重新打开excel-workbook之后，在我定义的第二个范围(K1:K10)中什么也没有发生.

My code is the following - when i save it, nothing happens in my second defined range (K1:K10), also after reopening the excel-workbook.

第二个条件格式范围(B1:B10)也没有任何反应:

Also nothing happens with my second conditional formatting range (B1:B10):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngObserve As Range, rngCell As Range

Set rngObserve = Intersect(Target, Range("E18:G18, K1:K10"))

If rngObserve Is Nothing Then
    Exit Sub
End If
For Each rngCell In rngObserve.Cells
    If Not Intersect(rngCell, rngObserve) Is Nothing Then
        If rngCell.Value = vbNullString Then
            rngCell.Interior.Color = xlNone
        ElseIf rngCell.Value < 1 Then
            rngCell.Interior.ColorIndex = 3 'red
        ElseIf rngCell.Value >= 1 Then
            rngCell.Interior.ColorIndex = 4 'green
        Else
            rngCell.Interior.ColorIndex = 3 'red
        End If
    End If
Next



Dim rngObserve As Range, rngCell As Range

Set rngObserve = Intersect(Target, Range("B1:B10"))

If rngObserve Is Nothing Then
    Exit Sub
End If
For Each rngCell In rngObserve.Cells
    If Not Intersect(rngCell, rngObserve) Is Nothing Then
        If rngCell.Value = vbNullString Then
            rngCell.Interior.Color = xlNone
        ElseIf rngCell.Value < 3 And rgncell.Value > 0 Then
            rngCell.Interior.ColorIndex = 6 'yellow
        ElseIf rngCell.Value >= 3 Then
            rngCell.Interior.ColorIndex = 4 'green
        Else
            rngCell.Interior.ColorIndex = 3 'red
        End If
    End If
Next

End Sub

推荐答案

如注释中所述，您只能有一个 Worksheet_Change 子例程.这段代码应该可以为您提供所需的信息:

As mentioned in the comments, you can only have one Worksheet_Change subroutine. This code should get you what you need:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngObserve As Range, rngCell As Range

'PGCodeRider comment: I'd set these to named ranges instead of hard-coded addresses
Set rngObserve = Intersect(Target, Range("E18:G18, K1:K10"))

    If Not rngObserve Is Nothing Then

        For Each rngCell In rngObserve.Cells

    If rngCell.Value = vbNullString Then
        rngCell.Interior.Color = xlNone
    ElseIf rngCell.Value < 1 Then
        rngCell.Interior.ColorIndex = 3 'red
    ElseIf rngCell.Value >= 1 Then
        rngCell.Interior.ColorIndex = 4 'green
    Else
        rngCell.Interior.ColorIndex = 3 'red
    End If

        Next rngCell

    End If


Set rngObserve = Intersect(Target, Range("B1:B10"))

    If Not rngObserve Is Nothing Then

        For Each rngCell In rngObserve.Cells

            If rngCell.Value = vbNullString Then
                rngCell.Interior.Color = xlNone
            ElseIf rngCell.Value < 3 And rngCell.Value > 0 Then
                rngCell.Interior.ColorIndex = 6 'yellow
            ElseIf rngCell.Value >= 3 Then
                rngCell.Interior.ColorIndex = 4 'green
            Else
                rngCell.Interior.ColorIndex = 3 'red
            End If

        Next rngCell

    End If
End Sub

这篇关于如果放置了值，则使用Excel-VBA为范围y着色；如果放置了值，则使用范围x着色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！