1 Attachment(s)
Second Code for Nelson using Admin Formula Idea
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 code
Code:
' 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
which, for the BEFORE worksheet “121” tells me that the yellow is an interior taken as a Double of 65535
InteriorColor.JPG http://imgur.com/MavnwLi
Attachment 1870
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