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.

 

[Belleke]

... Original writing has cleared ...
Moderator Note: You should able try to find something positive about this thread.
Otherwise, You could write eg Take Care.


Belleke As written in Forum Rules:

• Please use language which conveys respect, appreciation and love.

 

[sAhmed]

... Original writing has cleared ...
Moderator Note: You should able try to find something positive about this thread.
Otherwise, You could write eg Take Care.


Belleke As written in Forum Rules:

• Please use language which conveys respect, appreciation and love.

Hey, I am sorry if my language has hurt you in some way.

In all my messages I used to Thank you. I don't know in what way you think that I am not giving you respect.

I thought I had confused you with multiple instructions, so I have attached the original template just like my boss wants.

Anyway, Thanks again.

 

[Belleke]

It has nothing to do with your language, I lost time to find a solution dat was not representative,
The only thing I ask post a real example, instead wasting my time

 

[Belleke]

Try this,

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim rng As Range
Set rng = Range("B3:AG20,B24:AG41,B45:AG62")
t = Target.Value
If Not Intersect(rng, Target) Is Nothing And Target.Count = 1 Then
  If WorksheetFunction.CountA(Range(Target.Offset(, 1), Cells(Target.Row, "AG"))) = 0 Then
    Target.Resize(, 32).Interior.Color = xlNone
  Else
    Range(Target, Cells(Target.Row, (Target.Resize(, 32).Find("*").Column) - 1)).Interior.Color = xlNone
  End If
  If t <> "" Then
    If t = "L" Then
      a = 32
      kl = 6908415
    Else
      If Right(t, 2) = "BR" Then
        a = Replace(Replace(t, "BR", ""), ".", ",") * 4
        kl = 65535
      Else
        a = Replace(Split(t, "-")(0), ".", ",") * 4
        Select Case Left(Split(t, "-")(1), 2)
          Case "UK": kl = 11854022
          Case "US": kl = 15652797
          Case "AP": kl = 8696052
        End Select
      End If
    End If
    Target.Resize(, a).Interior.Color = kl
  End If
End If

End Sub