finding cell color in array loop

[TrippyTom]

Pardon my ignorance; I'm still learning.

I have data (calendar days) in an array (7 columns, 6 rows) in VB. I'm using a conditional formatting formula on the range to color the cell red if it's a holiday.

Is it possible to loop through the array to check if the day is colored red?

 

[TrippyTom]

Ok, thanks Greg. I look forward to seeing the change.

I was wondering how I should even go about this. Should my code even be in an array? Would a For/Next loop with a range be better? Or should I use some other method. Does it even matter? Is coding just a repesentation of personal style, or is one method better than others?

 

[TrippyTom]

In part of my code I'm trying to determine if the 15th or 30th falls on a weekend. How would i return the cell position in my array to show if it's in column 1 (sunday) or column 7 (saturday).

I have my month as a named range called "MAY".

 

[TrippyTom]

Ok, I decided I could use the WEEKDAY function like I did in another part of the worksheeet to decifer the day it falls on. However, why is my code not working? I have the calendar year in B5. It errors out on the line where I try to color the previous friday green.

For Each cell In Range("MAY")
   If cell.Value = 15 Or cell.Value = 30 Then
      If Weekday("5/15/" & Year(B5)) = 1 Then '(falls on sunday, move to last friday)
      Range(cell).Offset(-1, 6).Interior.ColorIndex = 4 'color cell green
      End If
   End If
Next

 

[TrippyTom]

Can anyone help me with this?

 

[TrippyTom]

Ok, what if I attack this problem at a different angle.

What if I include changing the font to BOLD and WHITE in the conditional format formula.

Then, would it work if I searched for the font color or weight instead of the cell's background color? Or is the simple fact it's STILL in a conditional format the part that's screwing me up?

 

[TrippyTom]

Hey Greg (or anyone else),

Select Case ColorIndexOfCF(c, False) = 11

Why doesn't this work? It's not allowing me to use a variable. It seems to want a specific cell reference.

Here's a bigger part of the code in case you wanted to see how i'm using it.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$5" Then
    Calculate
    For Each c In Union([E6:K11], [U6:AA11], _
                        [E15:K20], [M15:S20], [U15:AA20], _
                        [E24:K29], [M24:S29], [U24:AA29], _
                        [E33:K38], [M33:S38], [U33:AA38]).Cells
        
        c.Interior.ColorIndex = 2 'white
        c.Font.Bold = False
        c.NumberFormat = "General"
        c.Font.ColorIndex = cUnusedColorIdx
        
        Select Case c.Value
            Case Is = 15
                c.Interior.ColorIndex = 4 'green
                c.Font.Bold = True
                c.Font.ColorIndex = 2 'white
            Case Is = 30
            Select Case ColorIndexOfCF(c, False) = 11
                Case Is = True ' must be a Holiday
                MsgBox ("encountered a holiday")
                Case Else

                c.Interior.ColorIndex = 4 'green
                c.Font.Bold = True
                c.Font.ColorIndex = 2 'white
            End Select
                c.Interior.ColorIndex = 4 'green
                c.Font.Bold = True
                c.Font.ColorIndex = 2 'white
            Case Else
        End Select
    Next
End If

 

[TrippyTom]

I have FINALLY finished this project! If anyone cares to see it, I will send you the file. It's much too large and complex to show it on this board.

Features:

• Handles Leap Year (in February) automatically - 2004, 2008, etc.
• You can change the colors to whatever you wish and update
• Handles Paydays and moves them if they fall on Holidays or weekends
• Separate sheet for Birthdays

You wouldn't believe how long I spent trying to figure this out. Special thanks to those that helped me along the way; I couldn't have done it without you.