Format range based on cell value being between two dates.

[mickyh]

Good evening

I have a spreadsheet that tracks projects and hours allocated to them, It is a future planner so each week last week gets deleted and week twelve gets added. A lot of these projects are in schools and can only be carried out during the school holidays. I'd like to be able to highlight the columns which fall between these dates dynamically?

I have a formula that inputs the holiday "Name" on row 4 but I cannot work out a way to highlight the entire column if the dates in Row 5 fall between our holiday dates.

this is what I've tried so far which I realise if I could get it to work I would have to replicate it 12 times for each week. it highlights the selection irrespective of what dates are shown.
As always I'd be grateful for any advice, happy to desensitize the spreadsheet and post that if required.

Sub Macro1()
Dim RNG1 As Range, RNG2 As Range
Dim dt As Variant
Dim SD As Variant

Dim FD As Variant


'
' Macro1 Macro
Set RNG1 = Range("C6:I6")
Set RNG2 = Range("P36:V36")
SD = Range("P36")
FD = Range("W36")


With Sheets("master Sheet")
For Each dt In RNG1
If dt.Value >= SD And dt.Value < FD Then


    Range("C8:I31").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Else
    
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
  End If
  Next dt
  End With
  Range("A1").Select
  
End Sub

 

[bartmaster]

I would suggest to select all collumn and use conditional formatting with formula like:

=(Or(AND($C5>=[min date 1],$C5<=[max date 1]),AND($C5>=[min date 2],$C5<=[max date 2]),AND($C5>=[min date 3],$C5<=[max date 3]))

where c5 is the date you're looking at
min date 1 = start date of term
max date 1 = end date of term
etc etc.

I hope this helps.
If you struggle please insert XL2BB minisheet.

 

[mickyh]

Thanks Bartmaster
when I try to use this in a spreadsheet I get an error, unless I have made a mistake that I cannot see when changing your formula to suit my data?

=(Or(AND($C6>=P36],$C6<=W36),AND($C6>=P37],$C6<=W37,AND($C6>=P38,$C6<=W38))

 

[mickyh]

I would suggest to select all collumn and use conditional formatting with formula like:

=(Or(AND($C5>=[min date 1],$C5<=[max date 1]),AND($C5>=[min date 2],$C5<=[max date 2]),AND($C5>=[min date 3],$C5<=[max date 3]))

where c5 is the date you're looking at
min date 1 = start date of term
max date 1 = end date of term
etc etc.

I hope this helps.
If you struggle please insert XL2BB minisheet.

Sorry I saw the error after I sent the last message

=(OR(AND($C6>=P36,$C6<=W36),AND($C6>=P37,$C6<=W37,AND($C6>=P38,$C6<=W38))))

 

[mickyh]

Thanks again Bartmaster
I modified your formula to work on a week-by-week basis but I cannot format the selection (only the individual cells) using this method. I'm trying to just change the outline on the selection so that the other formatting is still visible. I'm not able to upload an xl2bb as every time I try excel crashes!

=OR(AND(K3>=$P$36,C3<=$W$36,$P$36<=K3),OR(AND(K3>$P$37,K3<=$W$37,$P$37<=K3), OR(AND(K3>=$P$38,C3<=$W$38,$P$38<=K3))))

 

[bartmaster]

@mickyh
Not sure what went wrong without seeing the file.
I would suggest selecting the range of cells to be checked for conditional formating in column K, then click on conditional formatting using custom formula you have.
just make sure that if you only want to check for the date in row 3 you have in your formula K$3, otherwise the conditional formatting will be checking against other rows.
If you want to apply the same for more column simply use 'format painter'.

 

[mickyh]

Thanks, Bartmaster