Condition in VBA if Date reported in Textbox is Higher or Lower

Question

I have a macro for the Date that the user entered in Textbox, for minor that the date of a cell of a given row, I will make a change in the information of a column of this row.

Sub test()

Dim data As String
Dim ultima As Long
Dim split As Range

data = TextBox4.Value
data_i = Format(data, "dd/mm/yy")

ultima = Range("B" & Rows.Count).End(xlUp).Row

Set split = Range("B7:B" & ultima)

For Each cell In split

    If cell < data_i Then
    
        cell.Offset(0, 4) = "Data Menor"
    
    End If
    
Next cell

End Sub

I know the VBA reads in American format. You would have to turn the dates of column B into "mm/dd/yy" to make the comparison with what the usurer wrote in Textbox?

What would be the best way to resolve this, please?

Thanks in advance! Hugs!

Answer 1

Even if your Excel is configured in English, there is no need to change the date formatting on your spreadsheet. I’ve done comparison dates with "Brazilian" standard in Excel configured in American English and it worked.

I don’t know how the spreadsheet works as a whole, but I wanted to suggest that:

• Date variables are declared as "As Date"
• Instead of using "Textbox" within a form, I would use the "inputbox" method, like this:

data = InputBox("Qual a data que você gostaria de inserir?")

Thus, you can manipulate dates using the proper properties, unlike what happens with Strings and compare dates in day/month/year format.

In addition, the "Cell. Offset(0, 4) = "Minor Date" is inserting the text "Minor Date" above the values of the column "Unit Price". I don’t know if this text should be positioned, but I think you will have to change the index used for the column.

I hope it helps.