Highlight a cell if it falls between 2 values on the worksheet AND text in another cell is x

[ffc2004]

Hi all,

I have a worksheet set up with dates that an area is booked for, and a calendar set up on the same sheet. Currently I've got it highlighting the date on the calendar if the date is entered on the worksheet by conditional formatting if values are between cell x and cell y, but what I would like to do is only highlight the date if they fall between these ranges AND cell z contains specific text. eg if cell O11 contains "Heath" then it would highlight the dates from J11-K11 on the calendar. Can this be done?

https://ibb.co/mhMhF3j is how it currently looks, I've tried <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">=IF($A$12=$O$11,AND(B11>=$J$11,B11<=$K$11))</code> but it only seems to work for the first row of dates.

 

[luculus67]

Hi all,

I have a worksheet set up with dates that an area is booked for, and a calendar set up on the same sheet. Currently I've got it highlighting the date on the calendar if the date is entered on the worksheet by conditional formatting if values are between cell x and cell y, but what I would like to do is only highlight the date if they fall between these ranges AND cell z contains specific text. eg if cell O11 contains "Heath" then it would highlight the dates from J11-K11 on the calendar. Can this be done?

https://ibb.co/mhMhF3j is how it currently looks, I've tried <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">=IF($A$12=$O$11,AND(B11>=$J$11,B11<=$K$11))</code> but it only seems to work for the first row of dates.

Remove absolute relationship ($)

 

[Scott T]

That's fantastic, thank you - final one (I think!)...I have conditional formatting set up on the calendar range in B9:H14 to put a border around the current date, the only problem is that it then removes the highlighting from the VBA if there is any?

Could you post the code you are using. You will need to adjust the code to test if the date is today and should be highlighted then it put in the boarders and highlight it. You can either keep the conditional formatting for when the cell is not highlighted or you can have the code do it and delete the conditional formatting.

 

[ffc2004]

Could you post the code you are using. You will need to adjust the code to test if the date is today and should be highlighted then it put in the boarders and highlight it. You can either keep the conditional formatting for when the cell is not highlighted or you can have the code do it and delete the conditional formatting.

The VBA is as below - I'd want it to border the current date in red if there is no matching date in rows N and O, and if there is a matching date then highlight as it currently does but still border the current date.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A9")) Is Nothing Or Not Intersect(Target, Range("A10")) Is Nothing Then
Dim cell As Range
Dim mrng As Range
Dim loc As Range
Dim rcount As Long
Set mrng = Range("B9:H14")
Set loc = Range("A10")
rcount = Cells(2, "J").End(xlDown).Row
mrng.Interior.Color = xlNone
For Each cell In mrng
For x = 2 To rcount
If Cells(x, "T") = loc And cell >= Cells(x, "N") And cell <= Cells(x, "O") Then
cell.Interior.Color = vbYellow
End If

Next x

Next cell
End If
End Sub

 

[Scott T]

Please use the code tags when posting code, this will preserve the indentations for easer reading. You can press the # sign in editor menu then past your code between the tags. If your code is already pasted you can select the code and then press the # in the menu.


Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A9")) Is Nothing Or Not Intersect(Target, Range("A10")) Is Nothing Then
    Dim cell As Range
    Dim mrng As Range
    Dim loc As Range
    Dim rcount As Long
    
    Set mrng = Range("B9:H14")
    Set loc = Range("A10")
    rcount = Cells(2, "N").End(xlDown).Row 'this should be the column with from dates
    mrng.Interior.Color = xlNone
    mrng.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone
    For Each cell In mrng
        For x = 2 To rcount
            If Cells(x, "T") = loc And cell >= Cells(x, "N") And cell <= Cells(x, "O") Then
                cell.Interior.Color = vbYellow
            End If
        Next x
        If cell = Date Then cell.BorderAround ColorIndex:=3, Weight:=xlMedium
    Next cell
End If
End Sub

 

[ffc2004]

Apologies, didn't realise! That works perfectly, thanks so much for all your help Scott.

 

[ffc2004]

Me again...

I tweaked the code slightly so that if a date is booked it highlights yellow, provisionally booked is green and cancelled is red (as per https://ibb.co/HzFvQ5P)

The problem I now have is that if a date is cancelled and highlights red, if it is subsequently booked then it won't highlight yellow to show this. Any ideas? (For example in the linked photo the cancelled date on 10th has since been booked so should show as yellow).


I'd have thought that having the cancelled part last on the confirmed/ prov/ cancelled part would mean that the others would take priority, but that doesn't seem to be the case?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A9")) Is Nothing Or Not Intersect(Target, Range("A10")) Is Nothing Then
Dim cell As Range
Dim mrng As Range
Dim loc As Range
Dim rcount As Long
Set mrng = Range("B9:H14")
Set loc = Range("A10")
rcount = Cells(2, "N").End(xlDown).Row
    mrng.Interior.Color = xlNone
    mrng.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone
    For Each cell In mrng
        For x = 2 To rcount
         
            If Cells(x, "T") = loc And Cells(x, "J") = "CONFIRMED" And cell >= Cells(x, "N") And cell <= Cells(x, "O") Then
                cell.Interior.Color = vbYellow
            End If
                                    
            If Cells(x, "T") = loc And Cells(x, "J") = "PROV" And cell >= Cells(x, "N") And cell <= Cells(x, "O") Then
                cell.Interior.Color = vbGreen
            End If
                  
            If Cells(x, "T") = loc And Cells(x, "J") = "CANCELLED" And cell >= Cells(x, "N") And cell <= Cells(x, "O") Then
                cell.Interior.Color = vbRed
            End If
           
        Next x
    If cell = Date Then cell.BorderAround ColorIndex:=5, Weight:=xlMedium
    Next cell
End If
End Sub