I have an excel sheet with data of planned and pending projects. In column "I" it states number of weeks required for completion of the project. Each row has separate projects listed. Column "J" to "BH" are blank and each cell in that column for that row indicates a week. So if the value in Column "I" Row "2" is 4, I want excel to color the cells inline with that project row. i.e. color columns J to M in row 2.

project sheet

I'm familiar with VBA as well. Any suggestions?


This can be done with Conditional Formatting. I assumed the columns continue "July (1 2 3 4); August (1 2 3 4) etc".

Select the cells you want to color and go to Home > Conditional Formatting > New Rule.

enter image description here

Then choose "Use a formula to determine which cells to format".

The formula is

=$I13>=(COLUMN(J13) - COLUMN($I13))

Basically, with (COLUMN(J13) - COLUMN($I13)) we check how many columns (weeks) there are from start. Then we compare it to the number of weeks.

enter image description here

Edit the cell format to your liking and click "OK" and "OK".

You should get something like this:

enter image description here

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.