Solved Excel help - changing cell fill based on another cells fill?

Microsoft corporation Excel 2013 32/64-b...
February 10, 2017 at 14:55:46
Specs: Windows 7, DDR2
Hi,

Really stuck here and looking for some help.

I have an excel sheet that I want to use to record the times people work a shift at work.

The sheet is set up as follows.

D3 has the workers name that I manually type in.
E3 has a drop down list of times of the day at half hour intervals (08:00, 08:30, 09:00 and so on up to 17:00). This is to represent the start time.
F3 is the exact same as E3. This is being used to represent the end time.

Underneath those cells on row 5 I have the timeline across the page.
A5 has 08:00. B5 has 08:30. C5 has 09:00 and so on again up to 17:00.
On row 6 directly underneath the times I have the conditional formatting currently set up so that when a start and end time are selected up the top the cell in row 6 under the chosen times turn green.

Here's the tricky part. How do I get the cells (in row 6) between the chosen times to also turn green so that basically if I select 08:00 start and 16:00 finish I have a line of green cells representing the shift?

Hope that makes sense? Help anyone?


See More: Excel help - changing cell fill based on another cells fill?

Report •

✔ Best Answer
February 10, 2017 at 17:41:43
I reread the original post, and I think my first answer was incorrect,
as it applies to Row 5.
In the original post Row 6 was specified as the target row.
If it is Row 6, which is a Blank Row with no data on it, then this should work:

1) Select your cell or range of cells, IE A5:S5 A6:S6
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=AND(A$5>=$E$3,A$5<=$F$3)

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK


EDIT: Put the wrong row number.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy



#1
February 10, 2017 at 16:09:59
I must be missing something here, as you say:

On row 6 directly underneath the times I have the conditional formatting currently set up so that when a start and end time are selected up the top the cell in row 6 under the chosen times turn green.

Then you ask to do the same thing:

How do I get the cells (in row 6) between the chosen times to also turn green so that basically if I select 08:00 start and 16:00 finish I have a line of green cells representing the shift?

Aren't you doing the same thing twice?

Using actual cell references might be helpful.

MIKE

http://www.skeptic.com/


Report •

#2
February 10, 2017 at 16:35:01
I believe that he is currently CF'ing e.g 8:00 and 10:00 based on the start and end times chosen in E3 and F3.

Those 2 values would have 3 non-CF'd cells in between them (8:30, 9:00 & 9:30)

Please CF those cells also. Thanks! ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
February 10, 2017 at 17:21:16
OK, that makes more sense, thanks DerbyDad

Try this an see how it works:

1) Select your cell or range of cells, IE A5:S5
2) On the ribbon click Conditional Formatting
3) Click on Between.
4) In the Left Input box enter your Start Time: Cell E3.
5) In the Right Input box enter your End Time: Cell F3
6) Click on the small Down Arrow in the color format box and select color.
7) Click OK
8) Click OK

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Related Solutions

#4
February 10, 2017 at 17:41:43
✔ Best Answer
I reread the original post, and I think my first answer was incorrect,
as it applies to Row 5.
In the original post Row 6 was specified as the target row.
If it is Row 6, which is a Blank Row with no data on it, then this should work:

1) Select your cell or range of cells, IE A5:S5 A6:S6
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=AND(A$5>=$E$3,A$5<=$F$3)

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK


EDIT: Put the wrong row number.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#5
February 11, 2017 at 14:59:24
Hi,

Thanks for the replies. I tried what you suggested however nothing happens at all?

When I select a start and end time nothing changes down the bottom on row 6


Report •

#6
February 11, 2017 at 15:15:34
Have both Start & End time cells, E3 & F3 been formatted as HH:MM?
Have all the times in Row 5, A5 thru S5 been formatted in the same way?
Additionally, have your times in the Drop Down been formatted in the same way?

I just recreated your sheet and using the Conditional Formatting in reply # 4
everything works for me.

Check to make sure all the formatting is correct and let us know.


MIKE

http://www.skeptic.com/


Report •

#7
February 11, 2017 at 15:20:24
Also, if you have any old Conditional Formatting rules related to Row 6,
delete them so there is no confusion.

MIKE

http://www.skeptic.com/


Report •

#8
February 11, 2017 at 15:28:30
Which suggestion did you try? Mike altered his first suggestion in Response #4. I just tried the Response #4 suggestion and it works fine.

My sheet looks like this, with G meaning the cell is Green:


        A      B      C      D      E      F      G      H      I      J
1
2
3 				   9:30  12:00
4
5     8:30    9:00   9:30  10:00  10:30  11:00  11:30  12:00  12:30  13:00
6                     G      G      G      G      G      G

If I choose different times from the Drop Downs, the green bar moves to match the time range shown in E3 & F3 unless E3 is greater than F3. When that occurs, Row 6 remains unfilled.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
February 12, 2017 at 02:19:00
Thank you so much. That's it working now.

This has saved me a lot of time at work.

Cheers 👍


Report •

Ask Question