Leave Schedule in Excel Format

[Mathew123]

Sir,

I have employees leave details as shown below.

If I fill the leave dates in Cells ‘C3’ and ‘D3’ and the status in ‘E3’, the relevent cells for those dates needs to be highlighted. These colours should also needs to change in line with the status in column ‘E3’.


The below schedule is done maually and please would you help to sort this with any formula.


Consider that Friday and Saturdays are Off and will be counted as part of vacation. Please help.

Tx

 

[Stacy Rueda]

If my understanding is correct, you may try conditional formatting.. But please explain your query clearly, so we can help you.

 

[Mathew123]

Yea...Conditional formatting would be fine.

I have an excel sheet prepared manually but am not able to attach or paste the screen shot of that..


[TABLE="width: 500"]
<tbody>[TR]
[TD]Sr. No[/TD]
[TD]Employee Name[/TD]
[TD]Leave Starts [/TD]
[TD]Leave Ends[/TD]
[TD]Status[/TD]
[TD]15 Jul 18[/TD]
[TD]16Jul 18[/TD]
[TD]17 Jul 18[/TD]
[TD]18 Jul 18[/TD]
[TD]19 Jul 18[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Emp. 1[/TD]
[TD]15-Jul-18[/TD]
[TD]17-Jul-18[/TD]
[TD]Approved[/TD]
[TD]COLOR IN GREEN[/TD]
[TD]COLOR IN GREEN[/TD]
[TD]COLOR IN GREEN[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.[/TD]
[TD]Emp. 2[/TD]
[TD]16-Jul-18[/TD]
[TD]18-Jul-18[/TD]
[TD]On Hold[/TD]
[TD][/TD]
[TD]COLOR IN YELLOW[/TD]
[TD]COLOR IN YELLOW[/TD]
[TD]COLOR IN YELLOW[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I will explain as follows:

I need to fill just the Starting date and end date of vacations in two cells.. the third cell will have status whether the leave is approved / on hold / re-scheduled etc. In line with the status, the adjacent columns including the start date and end date should be formatted in separate colors.

NB: please would you guide me how to paste a picture in the query so that i can paste the screen shot and will clarify you further.

Many Thanks,

 

[Perpa]

Mathew123,
Here is a screen shot I included using imgur.com an image hosting site I use so it could be added to this post.





Assuming I have the format (columns and rows) correct for your data, I made a macro that colors the cells below the Leave Start and Leave End dates for each employee.
You need to copy the macro below without the 'code' and '/code' lines into a standard module.
To do that press Alt+F11 to open the Visual Basic Editor. Then paste the code into the large window that opens.
Then close the Editor, and Save the file as macro enabled.


To run the macro you press Alt+F8 and select 'LeaveStatusColor', then 'Run'.


If the Status changes for any of the Employees, then run the macro again and the colors will be updated.
Perpa

Sub LeaveStatusColor()
Dim LR, rw, col As Long


LR = Cells(Rows.Count, "A").End(xlUp).Row


For rw = 2 To LR
    If Cells(rw, "E") = "Approved" Then
        ' Find column in row 1 where  'cells(1,col) = cells(rw,"C") '
         col = Rows(1).Find(What:=Cells(rw, "C"), SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
         
            Do While Cells(1, col) <= Cells(rw, "D")
                Cells(rw, col).Interior.ColorIndex = 4    GREEN
                col = col + 1
            Loop
        GoTo Passem
    End If


    If Cells(rw, "E") = "On Hold" Then
         'Find column in row 1 where  'cells(1,col) = cells(rw,"C") ' This is the Leave Start Date
         col = Rows(1).Find(What:=Cells(rw, "C"), SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column


            Do While Cells(1, col) <= Cells(rw, "D")
                Cells(rw, col).Interior.ColorIndex = 6   'YELLOW
                col = col + 1
            Loop
    End If
Passem:
Next
End Sub

 

[Mathew123]

Thanks Perpa..Its Working well for Emp.1..

but for Emp. 2 onwards - The colors are going all the way till the end and a pop up is showing "Run-time error '1004': Application-defined or object-defined error"

Would you help me to fix this please?

Thanks,
Aju

 

[Perpa]

Mathew123 (or is it Aju?),
I ran my code twice on the 3 lines of data as shown in my post and got the desired results.
It is difficult to analyse what is causing your error without seeing your basic data format.
As I said in my 1st response 'Assuming I have the format (columns and rows) correct for your data...'.


If you post a screen shot of the first 5 rows or so of your data including the header row, then I can have a look see why you are getting the error.


I use a 'Snipping Tool' to save a portion of the display, then upload that file to imgur.com. You need to copy the url link that imgur.com provides. That will be in the format: 'https://imgur.com/6TRSCU0'


When you post it back here don't forget to add the file extension '.jpg' because imgur.com does not do that: 'https://imgur.com/6TRSCU0.jpg'


Then select that string and look for the Mr Excel BB code to 'add a picture'.
That will put it in the proper format with IMG in front and /IMG at the end both surrounded by square brackets '[ and ]'.


One thought comes to mind, check to make sure the items in column E 'Status' have no leading or trailing spaces...it should be 'Approved' and not ' Approved', or 'Approved ', or ' Approved '.


I am travelling today, but I will check back when I have time this evening.
Perpa

 

[Perpa]

Mathew123/Aju,

Here is a Link to see 4 other methods to post a screen shot.


https://www.mrexcel.com/forum/about-board/508133-attachments.html


'Forum Tools' code written by Rory has an easy to use format. I prefer 'Copy Range' option
because it keeps the grid lines and displays in a smaller format. If you need to include formulas
then use the 'All formulas' option.
Hope this helps.
Perpa

 

[Mathew123]

Hello Perpa..
Officially am Mathew and my friends are calling me "Aju"...sorry for the confusion

I tried to put the screen shots here as u guided..but it is not working for me...
but this can be see in https://imgur.com/a/BHJ3Idn

Would you have a glance and advise please?

Many Thanks,

 

[Perpa]

Mathew,
Here is the results after running the revised code. Notice that I included the Leave Start dates '30-Jul-18' and '31-Jul-18' in row 1.

Excel 2007 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[TH]

G​

[/TH]
[TH]

H​

[/TH]
[TH]

I​

[/TH]
[TH]

J​

[/TH]
[TH]

K​

[/TH]
[TH]

L​

[/TH]
[TH]

M​

[/TH]
[TH]

N​

[/TH]
[TH]

O​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD]

Sr. No​

[/TD]
[TD] Name[/TD]
[TD]Leave Starts[/TD]
[TD]Leave Ends[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD]

30-Jul-18​

[/TD]
[TD]

31-Jul-18​

[/TD]
[TD]

1-Aug-18​

[/TD]
[TD]

2-Aug-18​

[/TD]
[TD]

3-Aug-18​

[/TD]
[TD]

4-Aug-18​

[/TD]
[TD]

5-Aug-18​

[/TD]
[TD]

6-Aug-18​

[/TD]
[TD]

7-Aug-18​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]Emp. 1[/TD]
[TD]

30-Jul-18​

[/TD]
[TD]

1-Aug-18​

[/TD]
[TD]UnPaid[/TD]
[TD][/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD]

2​

[/TD]
[TD]Emp. 2[/TD]
[TD]

31-Jul-18​

[/TD]
[TD]

2-Aug-18​

[/TD]
[TD]On Hold[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD]

3​

[/TD]
[TD]Emp. 3[/TD]
[TD]

1-Aug-18​

[/TD]
[TD]

3-Aug-18​

[/TD]
[TD]Approved[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5​

[/TD]
[TD]

4​

[/TD]
[TD]Emp. 4[/TD]
[TD]

2-Aug-18​

[/TD]
[TD]

4-Aug-18​

[/TD]
[TD]On Hold[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

6​

[/TD]
[TD]

5​

[/TD]
[TD]Emp. 5[/TD]
[TD]

3-Aug-18​

[/TD]
[TD]

5-Aug-18​

[/TD]
[TD]On Hold[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

7​

[/TD]
[TD]

6​

[/TD]
[TD]Emp. 6[/TD]
[TD]

4-Aug-18​

[/TD]
[TD]

6-Aug-18​

[/TD]
[TD]Approved[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]

Replace the previous code with the following using the 'Alt+F11' to open the Editor. Then run as before using the Alt+F8.
The results are as shown above.
Perpa

Sub LeaveStatusColor()
Dim LR, Lastcol, rw As Long
        
LR = Cells(Rows.Count, "B").End(xlUp).Row
Lastcol = Cells(1, Columns.Count).End(xlToLeft).Column


'Clear Previous filled cells
Range(Cells(2, "G"), Cells(LR, Lastcol)).Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("F2").Select


For rw = 2 To LR
    For col = 7 To Lastcol
        If Cells(rw, "E") = "Approved" Or Cells(rw, "E") = "UnPaid" Then
         'Find column in row 1 where  'cells(1,col) = cells(rw,"C")  'This is the Leave Start Date


            If Cells(rw, "C") = Cells(1, col) Then
         
                Do While Cells(1, col) <= Cells(rw, "D")
                    Cells(rw, col).Interior.ColorIndex = 4   'GREEN
                    col = col + 1
                Loop
            End If
        End If
    
        If Cells(rw, "E") = "On Hold" Then
         'Find column in row 1 where  'cells(1,col) = cells(rw,"C")  'This is the Leave Start Date


            If Cells(rw, "C") = Cells(1, col) Then
         
                Do While Cells(1, col) <= Cells(rw, "D")
                    Cells(rw, col).Interior.ColorIndex = 6   'YELLOW
                    col = col + 1
                Loop
            End If
        End If
    Next
Next
End Sub