Apply conditional formatting to a column if between range AND if between range in adjacent column

[seycharlotte]

Hi

I hope you can help. I'm trying to highlight a range of cells that fall between a range (>41) but only if the time in the adjacent column falls between two times. The sheet shows tidal ranges and I want it to highlight tide values above 41 (column B) but only between the hours of 09:00 and 17:00 (Column A). This would then be replicated across the sheet.
So in the example below B6 & B7 would be highlighted (in this case I don't include an example where the tide is above 41 but is outside of 09:00-17:00)
I have a small amount of ability in excel and have used conditional formatting before but I've reached my limit ha ha!
Many thanks in advance


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08:35[/TD]
[TD]26.50[/TD]
[/TR]
[TR]
[TD]09:00[/TD]
[TD]34.20[/TD]
[/TR]
[TR]
[TD]09:25[/TD]
[TD]38.17[/TD]
[/TR]
[TR]
[TD]09:50[/TD]
[TD]40.27[/TD]
[/TR]
[TR]
[TD]10:16[/TD]
[TD]41.90[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

try this formula for the CF

=AND(B1>41,A1>=TIME(9,0,0),A1<=TIME(17,0,0))

and apply for the whole column B

 

[seycharlotte]

Fab! Worked a treat. Many thanks. Another thing learnt!

 

[AlanY]

you're welcome