基于单元格颜色的条件格式化

Question

考虑一个包含3列的列表。

• 第1列包含日期列表。
• 第2栏有固定的数值，特殊的日子，如国定假日(复活节)和特殊的日子，如母亲节。
• 第3列保存用户定义的事件。

列2是通过VBA代码填充的，国定假日用相同的代码显示为蓝色，但特殊的日子(如母亲节)则不是。用户无法操作此列。

第3列由工作表本身上的用户填充。如果用户在第三列中添加事件，则行将通过条件格式显示为绿色。

问题是，如果一个事件与国庆节同时发生，蓝色的颜色就会被覆盖。

我的目标是添加第二个条件格式规则，该规则检查：

1. 添加用户定义的事件。
2. 行的颜色是蓝色的

如果这两个条件都满足了一个图案颜色，图案样式和背景颜色将被设置，所以我将得到两种颜色的组合。

如果只满足第一个条件，则只设置背景色。

简而言之:我需要一种检查单元格颜色的方法，并将其合并到条件格式规则中。

Answer 1

我这样做的方式只是放弃了条件格式，并添加了一个Worksheet_Change()事件。它相应地检查条件和格式。

请注意，这是一个非常艰巨的方法，但它完成了工作。如果条件格式中的构建包含一种将格式化作为可能条件的方法，就会容易得多。

作为一个副词，因为工作表是由代码添加的，所以它们本身并不包含这段代码，但它位于一个名为ClassModule的clsEvents中。

ClassModule中的声明

Public WithEvents chngSht As Worksheet

在添加工作表的Module中声明

Dim arrShts() as New clsEvents

添加工作表或打开工作簿时，将调用该子文件。

Sub shtEvents()
    Dim sht As Worksheet

    Erase arrShts
    ReDim arrShts(0)

    For Each sht In ThisWorkbook.Worksheets
        If Not sht.Name = "Menu" And Not sht.Name = "Tabellen" Then
            If UBound(arrShts) = 0 Then
                ReDim arrShts(1 To 1)
            Else
                ReDim Preserve arrShts(1 To UBound(arrShts) + 1)
            End If
            Set arrShts(UBound(arrShts)).chngSht = sht
        End If
    Next

End Sub

执行条件格式的实际代码。

Private Sub chngSht_Change(ByVal Target As Range)
    Dim sht As Worksheet
    Dim x As Long, y As Long
    Dim arrRange(1 To 4) As Range
    Dim blnWeekend As Boolean

    Set sht = Target.Parent
    With sht
        .Unprotect
        x = 1
        For y = 1 To 13 Step 4
            Set arrRange(x) = .Range(.Cells(4, y).Offset(0, 2), .Cells(.Rows.Count, y).End(xlUp).Offset(0, 2))          'Gather the 4 quarters of the year in 4 seperate ranges in an array.
            x = x + 1
        Next
        For x = 1 To 4                                                                                                  'Iterate through the quarters of the year
            If Not Intersect(Target, arrRange(x)) Is Nothing Then                                                       'Check if the event changed is in Q1, Q2, Q3 or Q4, or not
                blnWeekend = fnblnWeekend(Target.Offset(0, -2))                                                         'Check if the date falls in a weekend
                With .Range(Target, Target.Offset(0, -2)).Interior
                    Select Case True
                        Case Target = Empty And Target.Offset(0, -1) = Empty And Not blnWeekend                         'Event removed, no national holiday or other special day, and date is not a weekend
                            .Color = RGB(255, 255, 255)
                            .PatternColor = xlAutomatic
                            .Pattern = xlNone
                        Case Target = Empty And Target.Offset(0, -1) = Empty And blnWeekend                             'Event removed, no national holiday or other special day, and date is in a weekend
                            .Color = RGB(255, 255, 204)
                            .PatternColor = xlAutomatic
                            .Pattern = xlSolid
                        Case Target = Empty And Not Target.Offset(0, -1) = Empty And Not blnWeekend                     'Event removed, possibly national holiday or other special day, and dat is not in a weekend
                            Select Case True
                                Case Target.Offset(0, -1).Interior.Color = RGB(91, 155, 213)                            'Color of changed date indicates a National Holiday
                                    .Color = RGB(91, 155, 213)
                                    .PatternColor = xlAutomatic
                                    .Pattern = xlSolid
                                Case Target.Offset(0, -1).Interior.Color = RGB(198, 239, 206)                           'Color of changed date does not indicate a National Holiday
                                    .Color = RGB(255, 255, 255)
                                    .PatternColor = xlAutomatic
                                    .Pattern = xlNone
                            End Select
                        Case Target = Empty And Not Target.Offset(0, -1) = Empty And blnWeekend                         'Event removed, possibly a national holiday or other special day, and the date is in a weekend
                            Select Case True
                                Case Target.Offset(0, -1).Interior.Color = RGB(91, 155, 213)                            'Color of changed date indicates a National Holiday
                                    .Color = RGB(91, 155, 213)
                                    .PatternColor = xlAutomatic
                                    .Pattern = xlSolid
                                Case Target.Offset(0, -1).Interior.Color = RGB(255, 255, 204)                           'Color of changed date does not indicate a National Holiday
                                    .Color = RGB(255, 255, 204)
                                    .PatternColor = xlAutomatic
                                    .Pattern = xlSolid
                            End Select
                        Case Not Target = Empty And Target.Offset(0, -1) = Empty And Not blnWeekend                     'Event added, no National Holiday or other special day, and date is not a weekend
                            .Color = RGB(198, 239, 206)
                            .PatternColor = xlAutomatic
                            .Pattern = xlSolid
                        Case Not Target = Empty And Target.Offset(0, -1) = Empty And blnWeekend                         'Event added, no National Holiday or other special day, and date is in a weekend
                            .Color = RGB(255, 255, 204)
                            .PatternColor = RGB(198, 239, 206)
                            .Pattern = xlUp
                        Case Not Target = Empty And Not Target.Offset(0, -1) = Empty And Not blnWeekend                 'Event added, possibly National Holiday or other special day, and the date is not in a weekend
                            Select Case True
                                Case Target.Offset(0, -1).Interior.Color = RGB(91, 155, 213)                            'Color of changed date indicates a National Holiday
                                    .Color = RGB(91, 155, 213)
                                    .PatternColor = RGB(198, 239, 206)
                                    .Pattern = xlUp
                                Case Target.Offset(0, -1).Interior.Color = RGB(255, 255, 255)                           'Color of changed date does not indicate a National Holiday
                                    .Color = RGB(198, 239, 206)
                                    .PatternColor = xlAutomatic
                                    .Pattern = xlSolid
                            End Select
                        Case Not Target = Empty And Not Target.Offset(0, -1) = Empty And blnWeekend                     'Event added, possibly National Holiday or otheer special day, and date is not a weekend
                            Select Case True
                                Case Target.Offset(0, -1).Interior.Color = RGB(91, 155, 213)                            'Color of changed date indicates a National Holiday
                                    .Color = RGB(91, 155, 213)
                                    .PatternColor = RGB(198, 239, 206)
                                    .Pattern = xlUp
                                Case Target.Offset(0, -1).Interior.Color = RGB(255, 255, 204)                           'Color of changed date does not indicate a National Holiday
                                    .Color = RGB(255, 255, 204)
                                    .PatternColor = RGB(198, 239, 206)
                                    .Pattern = xlUp
                            End Select
                    End Select
                End With
                Exit For
            End If
        Next
        .Protect
    End With

End Sub

Answer 2

我认为您需要创建一个新的VBA函数，该函数可以确定单元格是否是特定颜色。例如，可以使用以下代码来确定某个区域中的单元格是否为蓝色：

If range.Interior.Color = RGB(0, 0, 256) Then
    colorAction = "Something"

然后，从单元格的宏调用VBA函数。

=CheckIfBlue(B5)

艾伦怀亚特有一个关于根据细胞颜色有条件地采取行动的好文章。

Answer 3

您必须添加3条规则

R1：和(用户定义的事件；not(假日))>绿色。

R2：和(非(用户定义的事件)；Holiday) >蓝色。

R3：和(用户定义事件；假日)>混合颜色。

选中“条件格式”对话框中最右边的“checkBoxs of R1，R2”对话框。