Easy way to mass conditional format?

[RedOctoberKnight]

Good evening,

First off, I would just like to say a big thank you to everyone who contributes to this forum. You're help has been much appreciated.

I'm trying to develop a visual chart for employee staffing and I'm wondering if there is a easy way to conditional format numerous cells on a mass scale.

Basically what i'm trying to do is the the following:

starting in cell B1 and moving across columns is my start times in 15 minute intervals. (my company uses A,B,and C to equal :15,:30 & :45.) EX: 10A would equal 10:15

so starting at midnight in column B and moving across columns, i have 0 in B1, 0A in C1, 0B in D1 , 0C in E1, 1 in F1 and so on all the way to 23C (24hr time)

What i'm looking to do is when I put a value into cell A2 (Let's say 3A), the cells in row 2 will highlight the color red underneath the times in row 1 in 8.5 hour intervals. So the cells in row 2 underneath the column that contains 3A all the way to 11C would highlight the color red.

I've been able to do accomplish this one start time at a time by highlighting each 8.5 block for that time and then setting a CF rule. It's just a very long process.

If this is the only way that it can be done than so be it, but if someone understands what i'm trying to accomplish and can do it in a much more efficient way, it would be much much appreciated.

As always, I appreciate all that everyone contributes.

Thanks.

 

[Marcelo Branco]

Question
What is the column whose header is 23C?

M.

 

[RedOctoberKnight]

3A is in column "O" and 11C is in column "AW"

 

[Marcelo Branco]

3A is in column "O" and 11C is in column "AW"

So 23C (last one) is in column CS.
If i'm right, assuming you are entering values in, say, A2:A20, try

Select B2:CS20 being B2 the active cell (the one not shaded after the selection)
Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format
Insert this formula
=AND($A2<>"",COLUMN(B2)>=MATCH($A2,$1:$1,0),COLUMN(B2)<=MATCH($A2,$1:$1,0)+34)

Format button and pick the format you want (Fill--> red)
Ok, Ok

Hope this helps

M.

 

[RedOctoberKnight]

Awesome! Thank you so much!

 

[Marcelo Branco]

You are welcome. Glad to help

M.

 

[RedOctoberKnight]

Say I wanted to do this but instead of conditional formatting it with a color, I simply have it place an "X" in the cells under the appropriate times? How would I accomplish that?

Thanks,

 

[Marcelo Branco]

Say I wanted to do this but instead of conditional formatting it with a color, I simply have it place an "X" in the cells under the appropriate times? How would I accomplish that?

Thanks,

Formula in B2 copied across till CS2 and down
=IF(AND($A2<>"",COLUMN(B2)>=MATCH($A2,$1:$1,0),COLUMN(B2)<=MATCH($A2,$1:$1,0)+34),"X","")

M.

 

[RedOctoberKnight]

Sorry, I'm kinda being needy here.

Is there a way to do this if say I had had a specific start time and end time. (not 8.5 hours every time).

So if I put a start time in of 4B and an end time of 9B, It would put an "X" in the cells between those times?

Again, thanks for everyone who contributes.

 

[Marcelo Branco]

What time is 48? And 98 (???)

M.