Complicated index and match for a booking VBA

Hi and welcome

You don't need VBA to do this. You can use Conditional Formatting with Index/Match formulas to accomplish this the easy way.

See attached.

BSB

Hello,

Thank you for your input.

However it is an assignment and I must use a VBA

thanks again

Well the INDEX/MATCH formulas you require are in the file I attached. Use those in VBA. Loop through each cell in your range of room numbers and apply them along with the formatting.
If formula A = true then red, elseif formula B = true then yellow, else green.

That should be enough of a steer. Can't be doing your homework for you!!

BSB

Thank you !

It helped me but I can't find the bug in my code. The problem seems to be in the red code

Here is the code:

Sub Next_button()

'-------------------------------------------------------
' Set the Next button
'-------------------------------------------------------

'get the current date in cell A1
Dim currentDate As Date
currentDate = Range("AG11").Value

'add one day to the current date
Dim nextDate As Date
nextDate = DateAdd("d", 1, currentDate)

'update the date in cell A1
Range("AG11").Value = nextDate

'-------------------------------------------------------
' Set the color match
' I need to match the cell AG11 to the row in Database and look for a name in the column if there is a name match with the room a change color
' If the cell selected = "name" --> red / if cell = "-" --> green / If cell ="locked" -->yellow
'-------------------------------------------------------

Dim Database As Range
Dim Cell As Range

' Define the range of the database
Set Database = ThisWorkbook.Sheets("Database").Range("C2:ND21")

' Loop through all the cells in columns D, I, M, R, and V
For Each Cell In ThisWorkbook.Sheets("Hotel Management System").Range("D2:D21,I2:I21,M2:M21,R2:R21,V2:V21")

' Check if the cell value is not empty
If Not IsEmpty(Cell.Value) Then

' Define the search values for the MATCH functions
Dim searchValue1 As String
Dim searchValue2 As String

searchValue1 = Cell.Value ' The value in the current column
searchValue2 = ThisWorkbook.Sheets("Hotel Management System").Range("AG11").Value ' The value in cell AG11

' Use the INDEX and MATCH functions to get the result
Dim result As Variant
result = Application.WorksheetFunction.Index(Database, _
Application.WorksheetFunction.match(searchValue1, ThisWorkbook.Sheets("Database").Range("A2:A21"), 0), _
Application.WorksheetFunction.match(searchValue2, ThisWorkbook.Sheets("Database").Range("C1:ND1"), 0))

' Check if the result is equal to "-"
If result = "-" Then
Cell.Interior.ColorIndex = 4 ' Set the background color to green
ElseIf result = "Locked" Then
Cell.Interior.ColorIndex = 6 ' Set the background color to yellow
ElseIf result <> "-" Then
Cell.Interior.ColorIndex = 3 ' Set the background color to red
End If

End If

Next Cell

End Sub

You're variables searchValue1 & searchValue2 are set as strings and therefore the result of the formula is an error.

Change them to Long data types and it should work.

Dim searchValue1 As Long
Dim searchValue2 As Long

BSB.