Searching a specific cell in columns and rows based on userform input vba

Question

I have a problem to write working code in vba excell. I'm writing a form which saves clients on visits. When my code was in shorter version it worked good, but it doesnt find the cell based on column with hours and row with days in february. The longer code showed how i thought it could work. I also atach ss of table in excel and my userform with name, surname, sex, phone number, service type, day and hour. In table red color shows taken visits. When adding visits cell change color on pink/blue depending on cell.

Shorter code:

    Private Sub CommandButton_Zatwierdz_Click()
    Range("E8").Select
    ActiveCell=TextBox_Name & " " & TextBox_Surname & Chr(10) & TextBox_PhoneNumber & Chr(10) & ComboBox_ServiceType
    If OptionButton_Woman = True Then
    ActiveCell.Interior.Color = RGB(255, 153, 204)
    ElseIf OptionButton_Men = True Then
    ActiveCell.Interior.Color = RGB(153, 204, 255)
    ElseIf OptionButton_Woman = False And OptionButton_Men = False Then
    MsgBox "You didn't choose a sex, choose a sex!"
    Exit Sub
    End If
    End Sub

Longer code:

    Private Sub CommandButton_Zatwierdz_Click()
    Range("E8").Select
    For ComboBox_Hour = Range("C9:C20").Find(what:=ComboBox_Hour).Select To ActiveCell = ""
    For ComboBox_Day = Range("E7:AF7").Find(what:=ComboBox_Day).Select To ActiveCell = ""
    If ActiveCell.Interior.Color = RGB(255, 0, 0) Then
    MsgBox "Sorry, this time is taken, please choose another"   
    ElseIf ActiveCell.Interior.TintAndShade = 0.599993896298105 Then
    ' here i was trying to get this color, but probably wrong
    ' With Selection.Interior
    '               .Pattern = xlSolid
    '               .PatternColorIndex = xlAutomatic
    '               .ThemeColor = xlThemeColorAccent6
    '               .TintAndShade = 0.599993896298105
    '               .PatternTintAndShade = 0
    'End With
    ActiveCell=TextBox_Name & " " & TextBox_Surname & Chr(10) & TextBox_PhoneNumber & Chr(10) & ComboBox_ServiceType
    If OptionButton_Woman = True Then
    ActiveCell.Interior.Color = RGB(255, 153, 204)
    ElseIf OptionButton_Men = True Then
    ActiveCell.Interior.Color = RGB(153, 204, 255)
    ElseIf OptionButton_Woman = False And OptionButton_Men = False Then
    MsgBox "You didn't choose a sex, choose a sex!"
    Exit Sub
    End If
    MsgBox "Your visit is now scheduled"
    Exit Sub
    End Sub
    
    

Answer 1

You don't need to use Find, calculate the cell position as an offset from the corner cell E8 Set cell = Range("E8").Offset((hr - 10) * 3, dy - 1)

Option Explicit

Private Sub CommandButton_Zatwierdz_Click()

    Const TBL = "E8" ' day 1 10:00
    
    Dim colorM As Long, colorF As Long
    Dim colorCell As Long
    colorM = rgb(153, 204, 255) ' blue
    colorF = rgb(255, 153, 204) ' pink
    
    ' check valid data
    If OptionButton_Woman = True Then
        colorCell = colorF
    ElseIf OptionButton_Men = True Then
        colorCell = colorM
    Else
        MsgBox "You didn't choose a sex, choose a sex!"
        Exit Sub
    End If
    
    Dim dy As Long, hr As Long, cell As Range, c As Long
    ' day
    dy = CLng(ComboBox_day)
    hr = Val(ComboBox_hour)
    Set cell = Range(TBL).Offset((hr - 10) * 3 / 2, dy - 1)

    MsgBox "hr=" & hr & " dy=" & dy & vbLf & "cell=" & cell.Address(0, 0, xlA1, True)
    
    c = cell.Interior.Color
    If c = colorM Or c = colorF Then
        MsgBox "Sorry, this time is taken, please choose another"
    Else
        cell.Value = TextBox_Name & " " & TextBox_surname & vbLf & _
                     TextBox_PhoneNumber & vbLf & ComboBox_ServiceType
        
        cell.Interior.Color = colorCell
        MsgBox "Your visit is now scheduled", vbInformation, cell.Address
    End If
   
End Sub