CODEWITHSUNDEEP
excelconditional-formatting
SilentRevolution
SilentRevolution

Reputation: 1513

Conditional Formatting based on cell color

Consider a list with 3 columns.

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:

  1. A user defined event is added
  2. 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.

Upvotes: 1

Views: 3669

Answers (3)

SilentRevolution
SilentRevolution

Reputation: 1513

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

Upvotes: 0

tdjprog
tdjprog

Reputation: 719

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.

Upvotes: 0

Jon Thoms
Jon Thoms

Reputation: 10807

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.

Upvotes: 1

Related Questions