conditional formatting a row in excel with certain intervals

[sAhmed]

Hi,

I am trying to create an Excel spreadsheet where I monitor each individual's availability based on the duration of the job assigned.

E.g. if ABC is an employee and he assigned 1-hour job, if I put 1 in one cell, the next four cells need to be highlight considering each cell to a 15 min duration

Attached is the excel for reference.

Can anyone please help me with any conditional formatting based on formula or VBA code?

Thanks.

 

[vletm]

One sample ...
As You've using times ... then You should write there times.
eg for 15min job, write 0:15 ... for 2hrs30min job, write 2:30

 

[Belleke]

Doest this help? With conditional formatting only

 

[sAhmed]

Doest this help? With conditional formatting only

Hi,

Thank you so much. It works. But is limited to only 1 hour duration.

Is it possible to make it any duration, like if someone took 2:15 mins job then it highlights that many cells. If someone took 7:45 mins job and so on. It should be any duration.

Do u get what I mean?

Thank you.

 

[sAhmed]

One sample ...
As You've using times ... then You should write there times.
eg for 15min job, write 0:15 ... for 2hrs30min job, write 2:30

Thank you for your help but seems VBA code have some malware. It is not wrkg in my system.

 

[sAhmed]

Thank you for your help but seems VBA code have some malware. It is not wrkg in my system.

Hi, my boss is asking me the status of this task Your urgent help is highly appreciated. Thanks in advance.

 

[vletm]

Excuse me? ... malware?
Are Macros enabled?

 

[Belleke]

solution with vba

 

[sAhmed]

solution with vba

Thank you for your timely help. But could you please help for further amendments as request by my boss.

Just assume Operator1 has assigned 1 hr job at 6am from UK region. If I enter the text 1-UK1111 (where 1111 is the Job id and it will vary job by job) below the 06:00 - 06:15, 4 cells should be highlighted till 06:46 - 07:00

Likewise, we have many regions, and each region Should highlight in different color.

IF operator1 go to 30 mins break at 07am, and I enter B0.5 below the 07:00 - 07:15, then two cells (07:00 - 07:15 and 07:16 - 07:30) should be highlighted in different color.

If someone is on leave and I enter L, then entire range should be in Red.

I hope you understand what I am looking for. Thanks in advance.

 

[Belleke]

Can you make an example (manually) what your boss wants?

 

[Belleke]

If I don't get an answer good luck

 

[sAhmed]

Hi,

I have attached the file whatever you have done earlier, and it was perfect in terms of duration.
Now I just want to display the region and job id along with the duration as I mentioned in the attached file and given below

Example: 1-UK1234, were

1 - refers to 1 hour duration
UK - refers to UK region job
1234 - Job ID

For UK - Green, US - Blue and AP - Orange.

And also want to implement break time

Example: Br0.5 - refers to half an hour break and it should shade the half an hour duration, BR0.25 - 15 mins break etc., It should be yellow color.

IF L - entire row should be shade in light red.

Hope it clarifies. Looking forward your help.

Thank you in advance.

 

[Belleke]

You are making this difficult, for yourself why 2 tables?
In my example hoover over ABC 0.25(B2 with the red triangle) or klik klik on GHI 7(D7), see the results and say what you prefer

 

[sAhmed]

Hi,

I am sorry If I have confused you.

Now I have attached you the real template and highlighted some rows manually as an example so that you better understand how my boss wants it.

I have given the following shading for each category

UK - Green
US - Blue
AP - Orange
Break - Yellow
Leave - Red

Thanks.