Booking system

Hey guys, first time posting, straight from work.

My boss wants me to elaborate a conference room booking system for events at the office, right away i saw that it could not be done only through formula, so i broke the immaginary wall and started messing with macros for the first time.

Here is what i managed to do so far: A table in wich you can select the room and the date through a dropdown list as well as other information. If local and date are not equal to previous entries, it copies the information to another sheet ("Database")

But thing is i dont know hot to do whats next.

I made this stupid calendar besides the table:

print.png

What i want to do is that when i select the local through the drop down list it runs a macro that looks for Local (A:A) and Date (B:B) in the Database sheet and highlight on the calendar the dates in wich the rooms are occupied.

Do u guys have any idea how can i do that?

OBS:Besides showing only day numbers inside the cell because of formating, the cells actually contain the date in dd/mm/yyyy format.

Try something like this (not tested)

Sub Ocupado()
    Dim cell As Range, rngCalandarDate As Range
    'Clear previous date highlights
    ActiveSheet.Range("F6:AJ11,F15:AJ20,F24:AJ29").Interior.ColorIndex = xlNone
    With Sheets("Database")
        If .FilterMode Then .ShowAllData
        'Filter the database for local
        .Range("A:B").AutoFilter Field:=1, Criteria1:=ActiveSheet.Range("C6").Value
        'Loop through each local date if any
        If .Range("B" & Rows.Count).End(xlUp).Row > 1 Then
            For Each cell In .Range("B2", .Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
                'Highlight matched dates on the calandar
                Set rngCalandarDate = ActiveSheet.Range("F6:AJ29").Find(cell.Value, , xlFormulas, xlWhole)
                If Not rngCalandarDate Is Nothing Then
                    rngCalandarDate.Interior.Color = vbYellow
                End If
            Next cell
        End If
        If .FilterMode Then .ShowAllData
    End With
End Sub

Originally Posted by _Gandour_

Strangely, nothing really happens, not even an error message

Can you attach an example workbook.

Try this...

Sub Ocupado()
    Dim cell As Range, rngCalandarDate As Range
    'Clear previous date highlights
    Range("F6:AJ11,F15:AJ20,F24:AJ29").Interior.ColorIndex = xlNone
    With Sheets("Database")
        If .FilterMode Then .ShowAllData
        'Filter the database for local
        .Range("A:B").AutoFilter Field:=1, Criteria1:=Range("C6").Value
        'Loop through each local date if any
        If .Range("B" & Rows.Count).End(xlUp).Row > 1 Then
            For Each cell In .Range("B2", .Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
                'Highlight matched dates on the calandar
                Set rngCalandarDate = Range("F6:AJ29").Find(DateValue(cell.Value2), , xlFormulas, xlWhole)
                If Not rngCalandarDate Is Nothing Then
                    rngCalandarDate.Interior.Color = vbYellow
                End If
            Next cell
        End If
        If .FilterMode Then .ShowAllData
    End With
End Sub

Originally Posted by _Gandour_

If Anyone wonders how i was able to use the dropdown as the macro activator: here it is (Dont know if its the best way)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("c6")) Is Nothing Then
    Select Case Range("c6")
        '"Local name": Macro name
        Case "Salão Nobre": Ocupado2
        Case "Auditório": Ocupado2
        Case "Sala de Treinamento 1": Ocupado2
        Case "Sala de Treinamento 2": Ocupado2
        Case "Laboratório de Informática": Ocupado2
    End Select
End If
End Sub

The Data Validation only allows a cell value from the list. This should work as well (not tested).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$6" And Target(1).Value <> "" Then Ocupado2
End Sub

You're welcome.

Put headers in row 1 of the Database sheet. The .Autofilter assumes the top row of the filter range is a header.