Conditional Format Q

[Seanie]

I wish to create a conditional format formula that will change the
background colour in a cell to Blue, if the value of another cell is
between to values (which are also detailed)

Cell I want to change is D8 on Sheet1
The dependent cell of D8 is in A8 on Sheet1
The two values which A8 must be between/or equal to is in F7 & H7 on
Sheet2

I just can't get my head around how to construct this

Thanks

 

[David Biddulph]

Select cell D8.
Format/ Conditional Formatting/ Formula Is/
=AND(A8>=Sheet2!F7,A8<=Sheet2!H7) if F7 is less than or equal to H7
or
Format/ Conditional Formatting/ Formula Is/
=A8=MEDIAN(Sheet2!F7,A8,Sheet2!H7) more generally

 

[Seanie]

Thanks

Slight issue it says I can't use references to other worksheets in CF

 

[Bob Phillips]

You can if you setup the range in the other sheet as a name
(Insert>Name>Define Name...) and use that name in the CF.

HTH

Bob

 

[Seanie]

Thanks Bob

I just referenced the relevant cells in the same sheet, Named Range
are a neat trick

One issue, what I am trying to do is give a visual of the 24 Hours in
a day and for each hour show whether the premises is open or not. Thus
in my example above a check for the hour of 7:00am, would be if this
hour is between the stated Open and closing, change the colour of
7:00am cell to "Blue". But what if trading hours straddle 2 days i.e.
Open = 6:30am and closing is next day at 2:00am?

Based on the formula above 7:00am would not be between 6:30am and
2:30am

Any ideas?