I created a userform in excel to be used for displaying data from an excel worksheet. When an item is selected from a combo box drop-down list, it populates all of the textboxes with the corresponding information. Many of the combo boxes contain dates. One box shows a FORECAST date of when a task will be completed and the other box shows the ACTUAL date of when that task was completed. Here is what I'm looking to do:
1) Compare the FORECAST textbox to the ACTUAL text box...if there is a date in the ACTUAL text box then do nothing but if there isn't a date in the ACTUAL box then do the following
2) f there is a date in the FORECAST box and the date is currently more than 6 days from today's date, then do nothing and keep background color as the normal WHITE
3) If there is a date in the FORECAST box and the date is currently less then 5 days from today's date, then turn the forecast box background color to YELLOW
4) If there is a date in the FORECAST box and If the forecast date is now past today's date then turn the forecast background color to RED
I'm really new to userforms and textboxes so not sure if the textbox needs to know that I'm working with dates and not text (or are dates considered text?) so that might be some of the issues I've been having. Thanks for your help.....
Private Sub UserForm_Initialize() Dim i As Long, LastRow As Long, ws As Worksheet Set ws = Sheets("Sheet1") LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row For i = 2 To LastRow Me.ComboBox1.AddItem ws.Cells(i, "A").Value Next i End Sub Private Sub ComboBox1_Change() Dim i As Long, LastRow As Long, ws As Worksheet Set ws = Sheets("Sheet1") LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row For i = 2 To LastRow If (Me.ComboBox1.Value) = ws.Cells(i, "A") Then Me.TextBox1 = ws.Cells(i, "B").Value 'Task 1 Forecast Date Me.TextBox2 = ws.Cells(i, "C").Value 'Task 1 Actual Completed Date Me.TextBox3 = ws.Cells(i, "D").Value 'Task 2 Forecast Date Me.TextBox4 = ws.Cells(i, "E").Value 'Task 2 Actual Completed Date End If Next i End Sub