Loop through and copy text from one cell to a cell in another column based on date match

I use conditional formatting to highlight cells in this Gannt chart. I want to put the Task Description in the first highlighted cell for the date range.

You can see how I am doing the conditional formatting, using the start and end dates, I compare the first day of the Month in the start date field and the last day of the Month in the end date field against the date in row 2.

What I want to do it, for each record, figure out which column matches the start date, and put the Task Description in that field.

I am attaching a sample document that first shows what I start with, and what I want to end up with.

You can just use formula (taken from your conditional formula, marked with red, and add some more conditions) like this :

Formula:

Please Login or Register  to view this content.

and if you want to use VBA so the formula in cell is wipe out (get value only) :

Please Login or Register  to view this content.

Another formula

D4
=IF(AND($B4<>"",MONTH($B4)=MONTH(D$2)),$A4,"")

AlphaFrog,

Very nice and short formula indeed, but probably there is a reason why OP choose that lengthy formula, for example there could be a chance that the table is still going right (for next years to come), so cell P2 = 01/01/2022, Q2 = 01/02/2022, need one more condition (e.g. And(month..=month, year.. = year..).

Originally Posted by karedog

...but probably there is a reason why op choose that lengthy formula, for example there could be a chance that the table is still going right (for next years to come), so cell p2 = 01/01/2022, q2 = 01/02/2022, need one more condition (e.g. And(month..=month, year.. = year..).

d4

Formula:

Please Login or Register  to view this content.

Originally Posted by karedog

You can just use formula (taken from your conditional formula, marked with red, and add some more conditions) like this :

Formula:

Please Login or Register  to view this content.

and if you want to use VBA so the formula in cell is wipe out (get value only) :

Please Login or Register  to view this content.

In trying to modify this for my production data I noticed that the vba function breaks if the duration is longer than two months. I am trying to understand what the offsets are doing and I just can't crack it.

EDIT: I figured out why it was breaking for longer durations, and that's because it was looking at the EOMONTH for the end date, and it should look at the start date, since I only what the value in that first month's cell.

But what I now cannot figure out is how this needs to be modified in my production data, where I have more cells to the left of everything, so the range starts at I3. I tried to methodically change the offsets to understand how they reference other cells with that column reference combination, but for some reason I just do not understand.

For example, you have COLUMN(D3)=4, what is the significance of that? Because that is always true. Isn't it? I don't understand what this accomplishes, even though it obviously works!

Hi James, many thanks for rep.points given.

Yes I missed to check that there could be more than 2 months span occurred.
If you want to use my style (using your conditional formatting formula, as part of the cell formula), you can change the code to this :

Please Login or Register  to view this content.

Or, if you want to use AlphaFrog's formula, which is obviously very short, then you can change the code to this :

Please Login or Register  to view this content.

As for your question :

Originally Posted by James Keuning

For example, you have COLUMN(D3)=4, what is the significance of that? Because that is always true. Isn't it?

It is true if the current cell is a cell located on column D, but the formula is copied across the whole range (column D:O), so for cells of another column (E:O), it is false.

To have a better picture, please try this :
- On a newly created sheet, on cell A2 put this formula =A1+1
- Now copy this formula to enlarged range, for example to range A2:C4
- The formula on cell B2 becomes =B1+1, on A3 becomes =A2+1
- In other word, Excel will self adjusted the formula on header range (in this case is the single cell A2, but in general, not necessary a single cell, it could be row or column also) to entire range.

The same thing applied to the VBA code, we input only the formula on the top left cell, and Excel will self adjusted the formula to the whole range.

You can examine this behavior by delete this code line :

Please Login or Register  to view this content.

so the code becomes :

Please Login or Register  to view this content.

and now if you go back to the sheet, you can examine the self adjusted formula by Excel.