Highlight a range of cells to match the date

[cmoore24]

I have a userform that opens when a persons name is double clicked. They enter a start date and an end date. My worksheet has every date entered across the top (from 8/01/2015 - 11/30/2016). I want to find a way to highlight the block of cells that someone enters into the userform, for example if someone in row 12 enters start: 8/03/2015 and end: 8/21/2015 in the userform then it will highlight every cell in say blue between those two dates in row 12. Can anyone help?

[Arkadi]

cmoore, can you upload a workbook we can work with?

[cmoore24]

I actually can't. Is there any way you can help without the workbook?

[Arkadi]

pretty tough... I have no idea how the book is structured, or how the form works now... I don't need real data, but if I don't know how it works now, then it is hard to duplicate your setup. can you maybe post the code for the form at least?

[cmoore24]

Private Sub cancelbutton_Click()

Unload Me

End Sub

Private Sub clearbutton_Click()

Call Userform_Initialize

End Sub

Private Sub eventbox_Change()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub savebutton_Click()

Dim sameRow As Long

sameRow = ActiveCell.Row

Cells(sameRow, 2).Value = startbox.Value
Cells(sameRow, 3).Value = endbox.Value

Unload Me

End Sub

Private Sub Userform_Initialize()

eventbox.Clear

With eventbox
.AddItem "Event 1"
.AddItem "Event 2"
.AddItem "Event 3"
.AddItem "Not Available"
End With

comment.Value = ""

startbox.Value = ""

endbox.Value = ""

End Sub

**For now I have it set up to just save the start and end dates in columns B and C respectively, but that was just to test to see if the form would work. The events I have place in there are supposed to determine what color the block will be highlighted, but my main concern is being able to actually get a block highlighted from the dates entered.

[Arkadi]

something like this perhaps?:

Please Login or Register  to view this content.

[cmoore24]

I see what you have tried to do there and the logic makes sense. But I put that into my code in the savebutton sub while taking everything else out except the unload and nothing showed up on the sheet. Do I need to activate the sheet or should I add that piece of code to what I already had?

[Arkadi]

whoops... supposed to find the date in the header.... my bad.... Headers in row 1 I assume?

Please Login or Register  to view this content.

[cmoore24]

The headers are row 2. But even when I changed the 1:1 to 2:2 it still didn't show up. Would setting the range from like Column D to Column XR (the dates start at D and go to XR in row 2) work?

[Arkadi]

cmoore... the headers are dates? you are formatting the startbox and endbox date to match the way it is in the headers? (sorry, gonna have to be a bit patient, since I don't have the book, I'm flying a bit blind)... we will get there...

[Arkadi]

Try this?

Please Login or Register  to view this content.

[cmoore24]

Arkadi,

That worked perfectly. Thank you so much for working through this with me even though I couldn't post the workbook, I really appreciate it.

[Arkadi]

Excellent, and my pleasure. Please take a moment to mark the thread as solved if you are satisfied? Thanks!