Help with a conditional formatting issue

**TigerEN1** · 06-21-2014, 05:30 AM

Hi Guys,

Hope some one out they can help me, as I am loosing my mind trying to work this out!!!!

I am trying to do a conditional formatting (fill with colour ) on a blank cell based on if data is correct from on cell in a different cell.

this is for staff Rota's

EG:

Cell B2 = Colour Fill if the data in Cell B1 match's Cell A2

Cell B 1:09.00-10.00 Cell C1 10.00-11.00

Cell A 2 09.00-18.00

Please can any one help?

or know of a some way to do a formula that can help?

I am begging, Please or my brain will explode with rage.

**haripopuri** · 06-21-2014, 05:38 AM

In conditional formatting, use a formula to decide which cell to format, enter this formula
=A2=B1

**etaf** · 06-21-2014, 05:40 AM

you would highlight the cell B2 and then use a formula
=a2=b1

why have you provided C1 info

did you want to apply to a range ?

perhaps you would attach a spreadsheet with the data and we can go from there

for 2007 or 2010 excel version
Conditional Formatting

Highlight applicable range >>
B2

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=B1=A2

Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

**TigerEN1** · 06-21-2014, 05:54 AM

Hi guys

thanks for help,

I just tried that and is was not working, don't know if it can not see it.

I hope that I have attached the sheet that I am working on

**etaf** · 06-21-2014, 06:10 AM

not sure what you are trying to do here - quite a detailed spreadsheet - needs some explanation

dont see how A2 , B2 A1 refers to this workbook

**TigerEN1** · 06-21-2014, 06:25 AM

Hi Etaf

that was an example.

from the work sheet I have attached .

first tab is a weekly rota, where I fill in peoples times that they are working that week.

Tab 2 is a brake down of the rota by hours on a Monday, Tab 3 Tue, Tab 4 Wed ETC.

I work with a lot of people, for who English is not there first langue, so I wish to show them there hours by filling in a block of colour showing them what hours that are due to work on that day. (as they say a picture paints a 1000 words)

so in the attached sheet Sarah (row 4) is working 09.00-18.00 so I want to fill H4 to Q4 cells with a colour but leave G,Q,R,S and T blank, as she is not working them hours on that day.

so I need to find a way that Cell H4 looks at H1 (09.00-10.00) and then look at E4 09.00-18.00) and says yes that is an hour that she will be working and fills the cell with a colour.

hope this clear?

**etaf** · 06-21-2014, 07:06 AM

A few changes required to your spreadsheet

you need to use find and replace and change all the . to : in the times
so for example 09.00-20.00 becomes 09:00 and 20:00

Then you can use a timevalue() to change the text to a real time and use > < =
also some of your times are just 2300 - so again use find/replace to change 2300 to 23:00 - i have done this on the rota sheet
i have done this on the workbook

Now providing you only have a time or D/O in the cells in column E the following will work OK before midnight

=IF($E4="D/O",FALSE, AND(TIMEVALUE(LEFT($E4,5))<=TIMEVALUE(LEFT(G$1,5)),TIMEVALUE(MID($E4,7,5))>TIMEVALUE(LEFT(G$1,5))))

and can be used as a conditional formatting

the issue will be if the shift goes over midnight
so
14:00-02:00
then it will nolonger work ..

do any shifts go over midnight ?

**TigerEN1** · 06-21-2014, 07:17 AM

Hi Etaf

thank you so much !!!!!!

you have really helped me on this, I cant even tell you how much this has been hurting my brain for the last week.

**TigerEN1** · 06-21-2014, 07:26 AM

thank you so much!

**etaf** · 06-21-2014, 07:27 AM

your welcome,
thanks for the rep

I assume the shifts do not go over midnight - if so then its not going to work

shifts from 24:00 - 04:00 will work
BUT
anytime before 24:00 and past 24:00 will not like
18:00-01:00

**TigerEN1** · 06-21-2014, 07:29 AM

no I will find a way around it, ending shits at 24.00 sorry 24:00

**etaf** · 06-21-2014, 07:36 AM

so no shifts go from before midnight to after midnight
if so ,then i can stop looking into a solution,

**etaf** · 06-21-2014, 07:41 AM

just noticed 24:00 is also a problem as that resolves to 0 on a timevalue()

so we can probably use an IF to change that to 23:59 instead
so IF ( the end of shift is 24:00 - change in the formula to timevalue("23:59")
then it will still say 24:00 in the shift in both columns

do we need to look at shifts over midnight
and do shifts run up to 24:00 ?

Help with a conditional formatting issue

LinkBack

Thread Tools

Rate This Thread

Display

Help with a conditional formatting issue

Re: Help with a conditional formatting issue

Re: Help with a conditional formatting issue

Re: Help with a conditional formatting issue

Re: Help with a conditional formatting issue

Re: Help with a conditional formatting issue

Re: Help with a conditional formatting issue

Re: Help with a conditional formatting issue

Re: Help with a conditional formatting issue

Re: Help with a conditional formatting issue

Re: Help with a conditional formatting issue

Re: Help with a conditional formatting issue

Re: Help with a conditional formatting issue

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Conditional Formatting Issue

Conditional Formatting Issue

Conditional formatting issue

Conditional formatting issue

conditional formatting issue

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions