Highlighting with a Calendar created

[OX_2005]

So I am not sure if what I am trying to do is possible. But here goes. I have 2 teams that every other week they work Sun, Mon, Tues. of one week and the next every other week they work Sun, Mon, Tues. I have a calendar created and what I am trying to do is if in column "C" it says Team 1 or 3 it will highlight the days they do not work that week. Is what I am looking to do even possible?

 

[NoSparks]

From your picture, how do we know which team is in which week of their schedule?
Is this Sun, Mon, Tues the only days they work in 2 weeks?
Is it reasonable to think this picture is just the start of your sheet and that the calendar goes on for the entire year?
Can you manually highlight the first 3 weeks for Teams 1 & 2 and post back so we know what your expectations are?

 

[OX_2005]

From your picture, how do we know which team is in which week of their schedule?
Is this Sun, Mon, Tues the only days they work in 2 weeks?
Is it reasonable to think this picture is just the start of your sheet and that the calendar goes on for the entire year?
Can you manually highlight the first 3 weeks for Teams 1 & 2 and post back so we know what your expectations are?

Sorry about that. This doc is set up for the entire year + years. Team 1 & 2 work Sun, Mon, Tues, & every other Wed. Team 3 Works Every opposite Wed, Thurs, Fri, & Sat. We started this rotation on 3/3/24 the blackout days are the days the teams are off and its just a continuing rotation like this. I put what March and April's breakdown would look like. Team 1 is day's & team 2 is nights, Then team 3 is days. I hope this can help with any confusion from the initial post.

 

[NoSparks]

OK, one month per sheet.

Are the dates in rows 7 and/or 8 real dates?
If you select cell G7 or G8 on the March sheet,
go to the vba Immediate Window,
and type in ?activecell.value2
and hit enter, does it give 45354 ?

 

[OX_2005]

I am trying to upload a Mini Sheet but waiting to get approval for Add-In from my administrators. But I will try to explain oh everything is currently working. The Months are not set up as individual sheets its only one sheet. The months are in a Data Validation from another tab as well as the years. The date is E8's formula is =DATEVALUE("1"&J4&V4). The date is F8 is =IF(E8="","",IF(EOMONTH($E$8,0)>=E8+1,E8+1,"")) and this continues all the way across just the green color font changes to the prior column.

This is using G8 cell

 

[NoSparks]

OK, thanks. That value2 for E8 was my biggest concern.
Will see what I can come up with.

 

[NoSparks]

Sorry OX, been working on this and afraid I have to admit defeat.
Highlighting that would over lap 2 months has me stumped.

Hopefully another member will happen by and take on this challenge.

 

[NoSparks]

Might have been over thinking things...
Try this

Sub OX_2005()
    
    Dim baseDate As Long
    Dim rng As Range, cel As Range
    Dim lc As Long, x As Long, teamRW As Long
    
' establish a starting point
baseDate = DateSerial(2024, 3, 6)

With ActiveSheet
    lc = .Cells(8, .Columns.Count).End(xlToLeft).Column
    Set rng = .Range(.Cells(8, 5), .Cells(8, lc))
    teamRW = .Columns(3).Cells.Find(What:="Team 1", LookIn:=xlValues, LookAt:=xlWhole).Row
    
    For Each cel In rng
        x = (cel.Value2 - baseDate) Mod 14
        Select Case x
        Case 0 To 3
            .Cells(teamRW, cel.Column).Resize(2).Interior.ColorIndex = 45
        Case 4 To 7
            .Cells(teamRW, cel.Column).Offset(2).Interior.ColorIndex = 45
        Case 8 To 10
            .Cells(teamRW, cel.Column).Resize(2).Interior.ColorIndex = 45
        Case 11 To 13
            .Cells(teamRW, cel.Column).Offset(2).Interior.ColorIndex = 45
        End Select
    Next cel
End With

End Sub

Hope it works for you
here's a link to my test file

 

[OX_2005]

Is this code based on having 1 sheet for each month or 1 sheet for multiple months and years? I tried the code and it work if I pressed play on the VBA but when I changed to the next month it did not update correctly when I clicked play on the VBA, But also I am looking to see if this will automatically update when I change months.

 

[NoSparks]

OK, forget all that and try conditional formatting

for Teams 1 & 2
=OR(AND(MOD(VALUE(E$8)-45354,14)>=3, MOD(VALUE(E$8)-45354,14)<=6), AND(MOD(VALUE(E$8)-45354,14)>=11, MOD(VALUE(E$8)-45354,14)<=13))

for Team 3
=OR(AND(MOD(VALUE(E$8)-45354,14)>=0, MOD(VALUE(E$8)-45354,14)<=2), AND(MOD(VALUE(E$8)-45354,14)>=7, MOD(VALUE(E$8)-45354,14)<=10))