In my excel sheet 'BOOKINGS' column A from A2 downwards contains dates in the following.…
- User
In my excel sheet 'BOOKINGS' column A from A2 downwards contains dates in the following. Here is a sample of A2 to A22. Friday 19/01/2024 17:30 Friday 19/01/2024 16:00 Saturday 20/01/2024 00:00 Sunday 21/01/2024 00:00 Monday 22/01/2024 15:00 Monday 22/01/2024 18:00 Monday 22/01/2024 19:00 Monday 22/01/2024 15:10 Monday 22/01/2024 18:00 Tuesday 23/01/2024 12:30 Tuesday 23/01/2024 12:30 Tuesday 23/01/2024 18:00 Tuesday 23/01/2024 15:10 Tuesday 23/01/2024 18:30 Tuesday 23/01/2024 20:00 Wednesday 24/01/2024 16:30 Wednesday 24/01/2024 17:00 Wednesday 24/01/2024 19:00 Thursday 25/01/2024 15:00 Thursday 25/01/2024 17:00 Friday 26/01/2024 17:30. The rows of dates usally continues for about three months of date values. I want to change the cell background colour to Green for the day with the earliest time in its series. Can you write a VBA code that can do this
- Assistant
Sure! You can use the following VBA code to change the cell background color to green for the day with the earliest time in its series:
Sub ChangeEarliestTimeColor() Dim ws As Worksheet Dim lastRow As Long Dim rng As Range Dim cell As Range Dim currentDate As Date Dim earliestTime As Date ' Set the worksheet to the "BOOKINGS" worksheet Set ws = ThisWorkbook.Sheets("BOOKINGS") ' Find the last row with data in column A lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Set the initial earliest time to a high value earliestTime = TimeValue("23:59:59") ' Loop through each cell in column A For Each cell In ws.Range("A2:A" & lastRow) ' Get the date from the cell currentDate = CDate(Left(cell.Value, InStr(cell.Value, " ") - 1)) ' If the current date has the earliest time, store it If TimeValue(cell.Value) < earliestTime Then earliestTime = TimeValue(cell.Value) End If ' If the next cell has a different date, change the background color of the cell with the earliest time to green If currentDate <> CDate(Left(cell.Offset(1, 0).Value, InStr(cell.Offset(1, 0).Value, " ") - 1)) Then Set rng = ws.Range(cell.Address, cell.End(xlDown)) For Each c In rng If TimeValue(c.Value) = earliestTime Then c.Interior.Color = RGB(0, 255, 0) ' Green color End If Next c earliestTime = TimeValue("23:59:59") ' Reset the earliest time End If Next cell End SubNote that you should change "BOOKINGS" in the code to the actual name of your worksheet.
To run this code, press
ALT+F11to open the VBA editor, insert a new module, and paste the code into the module. Then you can run theChangeEarliestTimeColormacro to change the cell background color. - User
error: Invalid procedure call or argument, on the line: If currentDate <> CDate(Left(cell.Offset(1, 0).Value, InStr(cell.Offset(1, 0).Value, " ") - 1)) Then
- Assistant
Apologies for the error. It seems that there is an issue with the function call on that line. You can try modifying the code as follows:
Sub ChangeEarliestTimeColor() Dim ws As Worksheet Dim lastRow As Long Dim rng As Range Dim cell As Range Dim currentDate As Date Dim earliestTime As Date ' Set the worksheet to the "BOOKINGS" worksheet Set ws = ThisWorkbook.Sheets("BOOKINGS") ' Find the last row with data in column A lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Set the initial earliest time to a high value earliestTime = TimeValue("23:59:59") ' Loop through each cell in column A For Each cell In ws.Range("A2:A" & lastRow) ' Get the date from the cell currentDate = CDate(Split(cell.Value, " ")(0)) ' If the current date has the earliest time, store it If TimeValue(cell.Value) < earliestTime Then earliestTime = TimeValue(cell.Value) End If ' If the next cell has a different date, change the background color of the cell with the earliest time to green If currentDate <> CDate(Split(cell.Offset(1, 0).Value, " ")(0)) Then Set rng = ws.Range(cell.Address, cell.End(xlDown)) For Each c In rng If TimeValue(c.Value) = earliestTime Then c.Interior.Color = RGB(0, 255, 0) ' Green color End If Next c earliestTime = TimeValue("23:59:59") ' Reset the earliest time End If Next cell End SubPlease try running the modified code and let me know if you encounter any further issues.