Afternoon,
I'm pretty new to using VBA and I fear I may have bitten off more than I can chew or have been a bit more overachieving with the task that I'm trying to do. I'm sure that it is something that can be done.
Ultimately, what I'm trying to do is this. This is a calendar for the entire pay year, It has multiple rows for each employee for each pay period (x 26 sections) that include days of week and numbered dates.
At the top in cells T2, an employee would select their name, put in the the start of the leave date (or the date that they would start training) in cell T3, the end of their leave date (or end of training) in cell T4, and the type of leave/training in cell T5.
The code would then search the rows 4,17, 30, 43, 56, 69, 82, 95, 108, 121, 134, 147, 160, 173, 186, 199, 212, 225, 238, 251, 264, 277, 290, 303, 316, 329, 342 for the first date in T3, Then search for the name of the employee in column A, highlight cell where the row and column meet, with the color that matches the training next to the training/leave in column P, then look for the end date in cell T4, and highlight every cell between those rows.
For instance, in the screen shot attached, It would find The date Jan 3, and employee Josie, highlight cell G7, in the Green color, then every offset cell until I7 (which is the end date in cell T4) in the same color.
Then it would save the workbook and clear the entries for the next employee to put their information in.
Maybe perhaps I need to do prompt message boxes. (which I know how to do).
But I can't get it to find the dates. I had some code that was able to search for dates through the rows, but it kept returning no date found.
Here is the code I had found and was testing:
Any ideas to help me not look like I'm in over my head, or should I just leave it as a workbook and make everyone search for their dates manually and color-code things on their own.
Thanks.
I'm pretty new to using VBA and I fear I may have bitten off more than I can chew or have been a bit more overachieving with the task that I'm trying to do. I'm sure that it is something that can be done.
Ultimately, what I'm trying to do is this. This is a calendar for the entire pay year, It has multiple rows for each employee for each pay period (x 26 sections) that include days of week and numbered dates.
At the top in cells T2, an employee would select their name, put in the the start of the leave date (or the date that they would start training) in cell T3, the end of their leave date (or end of training) in cell T4, and the type of leave/training in cell T5.
The code would then search the rows 4,17, 30, 43, 56, 69, 82, 95, 108, 121, 134, 147, 160, 173, 186, 199, 212, 225, 238, 251, 264, 277, 290, 303, 316, 329, 342 for the first date in T3, Then search for the name of the employee in column A, highlight cell where the row and column meet, with the color that matches the training next to the training/leave in column P, then look for the end date in cell T4, and highlight every cell between those rows.
For instance, in the screen shot attached, It would find The date Jan 3, and employee Josie, highlight cell G7, in the Green color, then every offset cell until I7 (which is the end date in cell T4) in the same color.
Then it would save the workbook and clear the entries for the next employee to put their information in.
Maybe perhaps I need to do prompt message boxes. (which I know how to do).
But I can't get it to find the dates. I had some code that was able to search for dates through the rows, but it kept returning no date found.
Here is the code I had found and was testing:
VBA Code:
Sub Find_Date()
Dim rng1 As Range
Dim dateStr As String
Dim dateToFind As Date
Dim foundDate As Range
'Get date as string value
dateStr = InputBox("Enter the date to be found")
'Convert string value to date format
dateToFind = DateValue(dateStr)
'Edit Sheet1 to your worksheet name
Set rng1 = Sheets("2025").Range(Cells(1, 1), _
Cells(Rows.Count, 4).End(xlUp))
Set foundDate = rng1.Find(what:=dateToFind, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not foundDate Is Nothing Then
foundDate.Select
Else
MsgBox dateStr & " not found"
End If
End Sub
Any ideas to help me not look like I'm in over my head, or should I just leave it as a workbook and make everyone search for their dates manually and color-code things on their own.
Thanks.