Hi
If I understood correctly, you can add one more column at the end to highlight which is the normal working day or holiday rather than coloring the cell.
Please find attached.
HTH
Hi
If I understood correctly, you can add one more column at the end to highlight which is the normal working day or holiday rather than coloring the cell.
Please find attached.
HTH
Cheers !
Excel Range to BBCode Table
Use Social Networking Tools If You Like the Answers !
Message to Cross Posters
@ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
Hi AdminOriginally Posted by Admin
I was a bit confused at first.
I see now what you have done now.
You have taken his AFTER and applied a SUMIF on the two columns I and J....
So first , You added then the extra column K with H for Holiday and N for normal day,
After WorksheetWorksheet: 121
Row\Col N N H N N
Then you have these formulas:
Row\Col
If Nelson wanted to do that he would still need to use part of my code to get the AFTER worksheet in its final form, as his start point , the BEFORE File is a bit different and needs a bit of manipulation based on his various criteria. ( Columns I and J needed to be adjsued:
BEFOREWorksheet: 121
Row\Col
_...........
By the way, I originally assumed that Holiday was a “Holy Friday thing”, ( So I was checking in my code for Friday and not yellow background color )but I am not sure if that is correct.
What I will do in the next posts to cover my back and to let your solution work is do another code, which checks based on the background colour being yellow for a holiday, then does the manipulation of the Worksheet, but also then after that includes your “H” or “N” in col.umn K, and while I am at it I will pastes in your formulas:
I expect then Nelson will have more than enough to keep him busy or confuse him
Alan
Last edited by DocAElstein; 02-05-2017 at 07:21 PM.
Hi Nelson,
This is another solution separate to mine form posts 5 6 and 7
Here is a new Full code: Sub IJAdjustKAddTotalAllWorksheet()
Post #20
http://www.excelfox.com/forum/showth...0072#post10072
The check criteria is now the background color, ( as alternative to Friday as in the first code ) '3d) We need to check Interior color . I got it from this quick codewhich, for the BEFORE worksheet “121” tells me that the yellow is an interior taken as a Double of 65535Code:' Rem Ref '_- http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp?p=10062#post10062 '_- http://www.excelfox.com/forum/showthread.php/2138-Understanding-VBA-Range-Object-Properties-and-referring-to-ranges-and-spreadsheet-cells?p=10012#post10012 Sub GetInteriorColor() ' https://msdn.microsoft.com/en-us/library/office/ff840499.aspx http://www.excelfox.com/forum/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10070#post10070 Dim ObjColor As Object Set ObjColor = Range("E3").Interior: Debug.Print ObjColor.Color ' Looks Yellow is Double 65535 in Immedite window ( Ctrl+g ) Set ObjColor = Range("E4").Interior: Debug.Print ObjColor.Color ' Looks like no color is Double 16777215 Dim RngDatesColors As Range ' Select RngDatesColors and hit Shift+F9 to get Range object in watch window. navigate by scrolling and opening with + signs the .Interor Object Set RngDatesColors = ThisWorkbook.Worksheets("121").Range("A1").Offset(0, 4).Resize(31, 1) ' .Interior shows only one value for the entire range which seems to be that for a cell with no background color as seen in the screenshot above all the cells have a colour End Sub
InteriorColor.JPG http://imgur.com/MavnwLi
InteriorColor.JPG
I make an extra array, arrK() to take the string values for the helper column K, to hold “H” for Holiday hrs or “N” for normal hrs
I no longer take in a dates array to check for Friday. Instead, I take in an Array, arrDteClr(), of the dates interior color. This is a bit inefficient as there is no Interior colors array in the Excel Range object. ( .Interior property for a Range object shows only one value for the entire range which seems to be that as for no color as seen in the screenshot above unless all the cells have a colour, in which case it gives the color for that )
So I have to take in each value in a new loop, 'InnerLoop for dates background colors ( so I could just as well have checked the worksheet, but I made an array for consistency as I am predominantly using arrays in my code).
Code section '3e) from the first code is not now used to calculate totals. ( and neither are the variables NOHrsV2 and HOHrsV2 which were used for those totals
Code section ‘3f) is modified to paste out the formulas from Admin instead of the totals values
In section ‘3g) there is a line which pastes out that help column
So in next post is just the BEFORE ( “File1 Before.xlsx” ) data range for the first worksheet “121”.
(as in Post #6
http://www.excelfox.com/forum/showth...0068#post10068 )
Then in the final 2 couple of post is the Output ( AFTER ) for the new code for one of the Worksheets ( “121” ). The code works on the same Input ( BEFORE ) as in Post #6
http://www.excelfox.com/forum/showth...0068#post10068
Last edited by DocAElstein; 02-05-2017 at 11:40 PM.
BEFORE ( “File1 Before.xlsx” ) data range for the first worksheet “121”.
(as in Post #6
http://www.excelfox.com/forum/showth...0068#post10068 )
Using Excel 2007 32 bit
Worksheet: 121
Row\Col SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER SOVEL JOY TEAM LEADER
Output data range ( AFTER ) for worksheet “121” after running either of the codes
Sub IJAdjustTotalAllWorksheet()
Or
Sub IJAdjustKAddTotalAllWorksheet()
Using Excel 2007 32 bit
Worksheet: 121
Row\Col SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER H SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER H SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER H SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER H SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER N SOVEL JOY TEAM LEADER H
Output values and formulas for output range for Worksheet "121" after running second code
Sub IJAdjustKAddTotalAllWorksheet()
( Second code is here: http://www.excelfox.com/forum/showth...0072#post10072
Output values as seen in the spreadsheet:Using Excel 2007 32 bit
Worksheet: 121
Row\Col TOTAL NO. OF DAYS -----> 30 For all Month no. of days we take as 30 only
Formula string values ( as seen in the Formula Bar ) :Using Excel 2007 32 bit
Worksheet: 121
Row\Col TOTAL NO. OF DAYS -----> 30 For all Month no. of days we take as 30 only
There you go. lots for you to try
before you tell us you wanted something different !
वहाँ आप का प्रयास करने के लिए बहुत सारे हैं।
संभवत: आप कुछ अलग चाहते हो सकता है
Alan
Dear Alan,
The code seems to be working fine
Can i use the same code for every month and if yes what changes i need to do from my end
I have around 800 sheets ...will the code work on all sheets in one go
Thanks,
Nelson
Posting Permissions
Reply With Quote
Bookmarks