Leave Schedule in Excel Format

Mathew123

New Member
Joined
Jun 20, 2018
Messages
15
Sir,

I have employees leave details as shown below.

D:\01 - WORK\New Work\New folder

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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Stacy Rueda

Board Regular
Joined
Jun 23, 2016
Messages
87
If my understanding is correct, you may try conditional formatting.. But please explain your query clearly, so we can help you.
 

Mathew123

New Member
Joined
Jun 20, 2018
Messages
15
Yea...Conditional formatting would be fine.

D:\01 - WORK\New Work\New folder

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


Sr. NoEmployee NameLeave Starts Leave EndsStatus15 Jul 1816Jul 1817 Jul 1818 Jul 1819 Jul 18
1Emp. 115-Jul-1817-Jul-18ApprovedCOLOR IN GREENCOLOR IN GREENCOLOR IN GREEN
2.Emp. 216-Jul-1818-Jul-18On HoldCOLOR IN YELLOWCOLOR IN YELLOWCOLOR IN YELLOW

<tbody>
</tbody>

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

Well-known Member
Joined
Dec 18, 2012
Messages
632
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




Code:
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

New Member
Joined
Jun 20, 2018
Messages
15

ADVERTISEMENT

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

Well-known Member
Joined
Dec 18, 2012
Messages
632
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

Well-known Member
Joined
Dec 18, 2012
Messages
632

ADVERTISEMENT

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

New Member
Joined
Jun 20, 2018
Messages
15
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

Well-known Member
Joined
Dec 18, 2012
Messages
632
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
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
1
Sr. No​
NameLeave StartsLeave EndsStatus
30-Jul-18​
31-Jul-18​
1-Aug-18​
2-Aug-18​
3-Aug-18​
4-Aug-18​
5-Aug-18​
6-Aug-18​
7-Aug-18​
2
1​
Emp. 1
30-Jul-18​
1-Aug-18​
UnPaid
3
2​
Emp. 2
31-Jul-18​
2-Aug-18​
On Hold
4
3​
Emp. 3
1-Aug-18​
3-Aug-18​
Approved
5
4​
Emp. 4
2-Aug-18​
4-Aug-18​
On Hold
6
5​
Emp. 5
3-Aug-18​
5-Aug-18​
On Hold
7
6​
Emp. 6
4-Aug-18​
6-Aug-18​
Approved

<tbody>
</tbody>
Sheet: Sheet2

<tbody>
</tbody>

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


Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,532
Messages
5,529,393
Members
409,870
Latest member
Well59
Top