Conditional Formatting in Excel

[rajesh2022]

I am looking to write excel formulas or VBA which will help me to perform the following actions.



1. Above screenshot, First column having date range in A3 and A4 Cells, i.e From (1-9-23) and to (3-24-23), i need to highlight these date range in colors from I column till end for all resource.
2. Once highlighted in any color, need to copy cell value from Project column (C Column), C4, here 5.

Above is example, but i will have similar date for almost 200 rows.

Any suggetions would be really helpful. Attaching sample file as well.

 

[vletm]

rajesh2022​

Cells D1 & H1 has [ Do It ]-buttons.
Those will give different results.
There could be some challenges
... if Your A-column dates not match with the 1st row dates
... Your Weeks won't match with 'full' weeks.
... I won't use Conditional Formatting

 

[rajesh2022]

rajesh2022​

Cells D1 & H1 has [ Do It ]-buttons.
Those will give different results.
There could be some challenges
... if Your A-column dates not match with the 1st row dates
... Your Weeks won't match with 'full' weeks.
... I won't use Conditional Formatting

Thanks Vletm, this is awesome. I am having some issue here.
1. if i added more resource name, then its not working.

Suggestion required: can we use VBA here? I am ok with any solution

Regards
Rajesh

 

[vletm]

rajesh2022​

You've written Above is example, but i will have similar date for almost 200 rows.
For me that means - four rows per ... row.
Is there any number of those resource names per row ... now?

 

[rajesh2022]

rajesh2022​

You've written Above is example, but i will have similar date for almost 200 rows.
For me that means - four rows per ... row.
Is there any number of those resource names per row ... now?

I am so sorry for the confusion. Format will not change. I mean columns Resource will be n numbers. Sometime it will be 2,3,4 ... n.

 

[vletm]

rajesh2022​

Maybe You mean that there can be any number of resources ...
and as well as
if there are something written below resources then there will be like '5's too.
> I updated my latest version ... which shows exact ranges.

 

[vletm]

rajesh2022​

Here is newer version, which shows current date too.
If You really have about 200 'rows' x 4row x 53 weeks data then Conditional Formatting uses ... 42400 formulas.

 

[p45cal]

A formula version.
In the attached, a formula in cell I2 which can be copied across and down (I've done that to cell BI17)
Also conditional formatting in the same cells, again the same CF can apply to the whole range.
It's this ability to allow the same CF and formula to apply across the whole range which has made the formulae a bit longer; maybe someone can come up with a shorter, more elegant version. I just played around until it came right.
The attached file is adapted from your attachment in your first mesage.
I've changed some of the dates in column A and some values in column C for testing.