vba timeline dates as range , select subrange based on date in 2 cell values Excel2013

Question

I am writing a timeline with the idea to populate the timeline with events from start date to end date of an event.

I have my dates starting from "E19" all the way to "RU19" (the end date column can be arbitrary. The dates are from 03-Apr-2016 to 31-Jul-2017.

Events have 3 columns:

• Start date: A22
• End date: B22
• Event name: C22

I set up a range from "E19" to last column of the document "RU19". Is it possible to set a subrange based on cell values that would iterate through the main range and return the value from start to end of the subrange?

So for example if my subrange start date in cell A22 is 05-Apr-2016 and end date in cell B22 is 08-Apr-2016 I would select a subrange "G19:J19".

Current code is:

Dim LastCol As Long
Dim startDate As Range
'find last column in the document
LastCol = Cells(19, Columns.Count).End(xlToLeft).Column
'set timeline range from start of date data to last column
Set startDate = Range(Cells(19, 5), Cells(19, LastCol))

Answer 1

Thanks for advice YowE3k but I think I've found a solution.

I create 3 Ranges, rngSelect, rngStart and rngEnd.

rngStart and rngEnd find the value of my Start and End cells in the main range and rngSelect simply puts the address values of the found dates and selects that.

Now I can go on with offsetting the range to be on the same row as the event and color the range.

code is as follows:

LastCol = Cells(19, Columns.Count).End(xlToLeft).Column

Set startDate = Range(Cells(19, 5), Cells(19, LastCol))
Set rngStart = startDate.Find(Range("A22"))
Set rngEnd = startDate.Find(Range("B22"))
Set rngSelect = Range(rngStart.Address, rngEnd.Address)

    rngSelect.Select

Answer 2

Are you after something like:

Dim StartDate As Range
Dim EndDate As Range
Dim EventRange As Range

Set StartDate = Cells(19, 5 + Cells(22, 1) - Cells(19, 5))
Set EndDate = Cells(19, 5 + Cells(22, 2) - Cells(19, 5))
Set EventRange = Range(StartDate, EndDate)
EventRange.Value = Cells(22, 3)

That's going to write over the dates that you have in row 19, so I'm guessing that's not exactly what you are after, but hopefully it will give you some clues to proceed with.

And, if you don't actually have dates in E19:RU19, just replace the "Cells(19, 5)" at the end of the "Set StartDate" and "Set EndDate" statements with the actual date I imagined from the question would be there, i.e.

Set StartDate = Cells(19, 5 + Cells(22, 1) - DateSerial(2016, 4, 3))
Set EndDate = Cells(19, 5 + Cells(22, 2) - DateSerial(2016, 4, 3))

Following comments in your answer, the following will select the subrange for each of rows 22 to 30. (Change the looping to suit your needs.)

For rowToProcess = 22 To 30
    Set StartDate = Cells(rowToProcess, 5 + Cells(rowToProcess, 1) - Cells(19, 5))
    Set EndDate = Cells(rowToProcess, 5 + Cells(rowToProcess, 2) - Cells(19, 5))
    Set EventRange = Range(StartDate, EndDate)
    'Do whatever you need with the subrange here, e.g. fill in cells with black colour to make it look like a project plan
    range(Cells(rowToProcess, 5), Cells(rowToProcess, Cells.SpecialCells(xlCellTypeLastCell).Column).Interior.Color = xlNone
    EventRange.Interior.Color = vbBlack
Next