Conditional Formatting based on cell color

Question

Consider a list with 3 columns.

Column 1 holds a list of dates.
Column 2 holds fixed values, special days such national holidays (Easter) and special days such as Mothersday.
Column 3 holds user defined events.

Column 2 is populated via VBA code and the national holidays are colored blue by the same code but the special days such as Mothersday are not. The user can not manipulate this column.

Column 3 is populated by the users on the sheet itself. If the user adds an event in the third column, the row is colored green by means of conditional formatting.

The problem is that if an event coincides with a national holiday, the blue color is overwritten.

My goal is to add a second conditional formatting rule which checks for:

A user defined event is added
that the color of the row is blue

If both conditions are met a pattern color, pattern style and background color will be set so I'll get a combination of both colors.

If only the first condition is met, only the background color will be set.

In short: I need a way to check for a cell color and incorporate this in a conditional format rule.

score 1 · Answer 1 · answered Dec 05 '17 at 22:52

1

I think that you need to create a new VBA function that can determine whether or not a cell is a particular color. For example, the following code could be used to determine if a cell in a range is blue:

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

Then, call the VBA function from the cell's macro.

=CheckIfBlue(B5)

Allen Wyatt has a good article on conditionally taking an action depending on a cell's color.

answered Dec 05 '17 at 22:52

entpnerd

10,049
8
47
68

It appears that in order to accomplish my goal I may need to ditch the conditional formatting altogether and add in a `Worksheet_Change()` routine which checks for the conditions. The problem however is that the worksheets themselves are added and built up by code. – SilentRevolution Dec 05 '17 at 23:01
@SilentRevolution - If you have a hidden worksheet complete with event code to act as a template when you add a new worksheet, the new worksheet will carry a new worksheet code sheet. You could also try a Workbook_SheetChange that covers a variety of worksheets. – Dec 05 '17 at 23:19
@Jeeped, that is certainly an option but at the moment I've solved that particular issue by using and alterd version of [this solution](https://stackoverflow.com/questions/10224511/assign-code-to-a-button-created-dynamically). – SilentRevolution Dec 05 '17 at 23:28

score 0 · Answer 2 · answered Dec 06 '17 at 03:45

0

You must add 3 rules

R1: and(user defined event; not(Holiday)) > green.

R2: and(not(user defined event); Holiday) > blue.

R3: and(user defined event; Holiday) > mixed color.

Check the most right checkBoxs of R1, R2 in conditional format dialog box.

answered Dec 06 '17 at 03:45

tdjprog

706
6
11

This would work if the second column was only filled with national holidays which all would be blue. This is, however, not the case. Only some entries, but not all, in the second column are public holidays and only those are are blue and require a mixed color if they coincide with a user defined event. – SilentRevolution Dec 06 '17 at 12:23

score 0 · Accepted Answer · answered Dec 11 '17 at 21:29

The way I've went about this was just ditching the conditional formatting all together and adding a Worksheet_Change() event. Which checks for the conditions and formats accordingly.

Mind you that this is a very arduous method but it got the job done. It would be so much easier if the build in conditional formatting would incorporate a way to include formatting as a possible condition.

As a sidenote, because the sheets are added by code, they themselves do not include this code but this sits in a ClassModule called clsEvents.

Declaration in the ClassModule

Public WithEvents chngSht As Worksheet

Declaration in the Module where the sub is which adds the worksheet

Dim arrShts() as New clsEvents

When a sheet is added, or the workbook is opened this sub is called

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

The actual code which does the conditional formatting.

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

Conditional Formatting based on cell color

3 Answers3

You must add 3 rules