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.

 

[Scott T]

When entering a formula into CF you do not need to use IF

Is this what you want?

=AND($A$12=$O$11,B11>=$J$11,B11<=$K$11)

 

[ffc2004]

When entering a formula into CF you do not need to use IF

Is this what you want?

=AND($A$12=$O$11,B11>=$J$11,B11<=$K$11)

This only seems to work on the first row of dates?

 

[Scott T]

Try this VBA code

Right click on the tab and select view code.
past the below code into the VBA editor

When you change A12 the code will run. If you put in a location in A12 that is not in the list then all the colors will be cleared and nothing will be highlighted.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A12")) Is Nothing Then
Dim cell As Range
Dim mrng As Range
Dim loc As Range
Dim rcount As Long
Set mrng = Range("B11:H15")
Set loc = Range("A12")
rcount = Cells(11, "J").End(xlDown).Row
    mrng.Interior.Color = xlNone
    For Each cell In mrng
        For x = 11 To rcount
            If Cells(x, "O") = loc And cell >= Cells(x, "J") And cell <= Cells(x, "K") Then
                cell.Interior.Color = vbYellow
            End If
        
        Next x
    
    Next cell
End If
End Sub

 

[ffc2004]

Try this VBA code

Right click on the tab and select view code.
past the below code into the VBA editor

When you change A12 the code will run. If you put in a location in A12 that is not in the list then all the colors will be cleared and nothing will be highlighted.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A12")) Is Nothing Then
Dim cell As Range
Dim mrng As Range
Dim loc As Range
Dim rcount As Long
Set mrng = Range("B11:H15")
Set loc = Range("A12")
rcount = Cells(11, "J").End(xlDown).Row
    mrng.Interior.Color = xlNone
    For Each cell In mrng
        For x = 11 To rcount
            If Cells(x, "O") = loc And cell >= Cells(x, "J") And cell <= Cells(x, "K") Then
                cell.Interior.Color = vbYellow
            End If
        
        Next x
    
    Next cell
End If
End Sub

This is great, thank you - the only amendment I would need is that I also change A11 to change the month?

 

[Scott T]

Change the first line to

If Not Intersect(Target, Range("A12")) Is Nothing Or Not Intersect(Target, Range("A11")) Is Nothing Then

 

[ffc2004]

Change the first line to

If Not Intersect(Target, Range("A12")) Is Nothing Or Not Intersect(Target, Range("A11")) Is Nothing Then

Thank you! That works perfectly.

 

[ffc2004]

Another one - is there any way to make the range a series of text boxes instead of a range of cells?

 

[Scott T]

I do not use text boxes much but I would think it could be done.

What would be in the text boxes?

 

[ffc2004]

I do not use text boxes much but I would think it could be done.

What would be in the text boxes?

The calendar range I have set up currently would be a set of grouped text boxes - the calendar is currently on the left half of the sheet and the table of data on the right. When I filter the data when needed it then hides the rows with calendar data, but I can't move the calendar above the table without messing up the column widths I need so was looking to create the calendar with text boxes linked to the cell reference on another sheet.

Sorry if that doesn't make much sense!