VBA & Macro Problems

Use Excel VBA to Find Available Vehicles by Date

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 869 views

Question details

Create a vehicle booking workflow that checks availability for a selected date and highlights available vehicles in green.

Find Available Vehicles by Date with Excel VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A booking sheet lists vehicles in rows and dates in columns, with cell colors indicating bookings.
Observed behavior
The desired result is to select a date and see the available vehicles and their numbers highlighted in green.
Before you start

Check whether bookings are colored manually or by conditional formatting, and agree on one exact fill color for booked cells. The example below assumes vehicle names and numbers are in columns A and B, dates start in C1, and booked cells are red.

Solution 1Recommended

Highlight available vehicles when you double-click a date

Use a worksheet event to check the displayed booking color in the selected date column and highlight available vehicle labels.

The example treats a cell as booked only when its displayed fill is RGB(255, 0, 0). Change the RGB values in the code to match the exact booked color used on your sheet. DisplayFormat reads the visible fill, including conditional formatting.

The macro clears existing direct fills in columns A and B before applying green. Conditional formatting on those label cells can override the green display.

1
Open the worksheet code

In Excel, open the booking workbook, choose Developer > Visual Basic, and double-click the booking worksheet in the Project pane. If Developer is hidden, enable it in Excel's ribbon settings.

2
Add the event code

Paste this code into the worksheet's code window: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim lastRow As Long Dim r As Long Dim bookedColor As Long If Target.CountLarge > 1 Then Exit Sub If Target.Row <> 1 Or Target.Column < 3 Then Exit Sub If Not IsDate(Target.Value) Then Exit Sub Cancel = True bookedColor = RGB(255, 0, 0) lastRow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row If lastRow < 2 Then Exit Sub Me.Range("A2:B" & lastRow).Interior.Pattern = xlNone For r = 2 To lastRow If Me.Cells(r, Target.Column).DisplayFormat.Interior.Color <> bookedColor Then Me.Range(Me.Cells(r, "A"), Me.Cells(r, "B")).Interior.Color = RGB(0, 255, 0) End If Next r End Sub

3
Save and test

Use File > Save As to save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Enable macros if prompted, then double-click a date in row 1 to highlight vehicles whose booking cells are not red.

Highlight available vehicles when you double-click a date
Adjust the layout and booking rule: If your dates, vehicle labels, or booked color differ, update the row, column, range, and RGB references in the code. A cell with any displayed color other than the specified booked color is treated as available.
Free Microsoft Office alternative

Manage vehicle booking sheets with WPS Spreadsheets

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel workbooks and maintain vehicle booking tables in WPS Spreadsheets for an easier transition; it does not fix Excel VBA issues or guarantee that Excel macros will run unchanged.

Organize vehicle booking dates and availability in a spreadsheetOpen and edit compatible Microsoft Excel XLSX filesUse a familiar spreadsheet interface for easier migrationWork with a free, lightweight Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Can the macro detect colors applied by conditional formatting?

Yes. The example uses DisplayFormat.Interior.Color to compare the cell's displayed fill with the specified booked color, including a fill produced by conditional formatting.

How do I change the color that means a vehicle is booked?

In Developer > Visual Basic, open the booking worksheet's code and replace RGB(255, 0, 0) in the bookedColor assignment with the exact RGB values of your booked fill.

Why is a vehicle highlighted even though it is booked?

The displayed booking-cell color may not match the RGB value in the code. Check the cell's fill or conditional formatting rule and update bookedColor. The example treats every other displayed color as available.

Why does the macro clear my vehicle label colors?

The code removes direct fills from A2:B before applying the new green highlights. Change that range or use a different output area if those cells contain colors you need to preserve.