• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Gant Chart in excel with conditional formatting for calendar - please help

Alina_T

Member
Hello! Please help me with the attached file:

  1. I would need a VBA in cell H1 that, upon changing the month, will display only that specific month in the calendar, instead of stretching across all 12 months.
  2. I would like the calendar to be colored for each project based on the start date (Data Start) and due date (Deadline).
Thank you very much!
 

Attachments

  • proiecte_evidenta_in lucru.xlsx
    68.3 KB · Views: 3

Alina_T

You've opened Your thread in Ask an Excel Question, but ...
... You've written would need VBA ... hmm?
Or could this belong to Discuss Data Visualizations and Charting?
Could something do without conditional formatting?
 
See attached where I've done your number 1 requirement.
Data validation in cell H1.
Delete cell H1 contents or choose All from the dropdown to see all dates.
Worksheet_Change event code in that sheet's code-module.
(For debugging/explanation purposes, code called StepByStepWorksheet_Change takes it step by step. It's not used)
 

Attachments

  • Chandoo56719_proiecte_evidenta_in lucru.xlsm
    76.3 KB · Views: 7

Alina_T

You've opened Your thread in Ask an Excel Question, but ...
... You've written would need VBA ... hmm?
Or could this belong to Discuss Data Visualizations and Charting?
Could something do without conditional formatting?
I'm sorry, you are right. I apologize for the mistake. Thank you for pointing it out.
 
See attached where I've done your number 1 requirement.
Data validation in cell H1.
Delete cell H1 contents or choose All from the dropdown to see all dates.
Worksheet_Change event code in that sheet's code-module.
(For debugging/explanation purposes, code called StepByStepWorksheet_Change takes it step by step. It's not used)
Thank you very much! I am testing it now. Thank you so much for your help.
 
See attached where I've done your number 1 requirement.
Data validation in cell H1.
Delete cell H1 contents or choose All from the dropdown to see all dates.
Worksheet_Change event code in that sheet's code-module.
(For debugging/explanation purposes, code called StepByStepWorksheet_Change takes it step by step. It's not used)
You are really great! It works wonderfully. Thank you again! Is there anything I can do to return the favor? What you've done really helps me a lot. I still need to find a solution for coloring the rows based on the start date and deadline.
 

Alina_T

Did You notice my the last question?
Here one sample as I tried to offer.
# Use spinner to select year
# cell H1 for selecting month
# cell J1 for selecting number of months to show (1...12)
# there are different colors/rules for some different cases
 

Attachments

  • proiecte_evidenta_in lucru.xlsb
    34.4 KB · Views: 2
@

vletm

Alina_T

Did You notice my the last question?
Here one sample as I tried to offer.
# Use spinner to select year
# cell H1 for selecting month
# cell J1 for selecting number of months to show (1...12)
# there are different colors/rules for some different cases
No, I haven't seen your last question :) I stopped at my mistake :) Now that I've seen your message, I realized that I overlooked it. No, I don't necessarily have to use conditional formatting; I didn’t know any other possibilities. What you’ve done in the file looks great; how did you do it? It looks like what I need to do. Thanks a lot, guys!
 
I have another question for your brilliant minds :) I would like to upload it to Drive so that my colleagues can access it, but I've noticed that the macro doesn't work. Do you know of another way it can be used by colleagues in real time without freezing Excel? Or how could I upload it to Drive and have the macro function?
 
@

vletm


No, I haven't seen your last question :) I stopped at my mistake :) Now that I've seen your message, I realized that I overlooked it. No, I don't necessarily have to use conditional formatting; I didn’t know any other possibilities. What you’ve done in the file looks great; how did you do it? It looks like what I need to do. Thanks a lot, guys!
I have noticed this message when I try to change the year. Maybe I didn't understand what I need to do.1713543737528.png
 

Alina_T

It would be good to read always all texts.
In some cases there could be something which You should know too.
I noticed that previous version has one line which should modify - delete Your previous version and use this one.
# Drive ... okay ....
Did You write that 'minor "Drive" detail' before?
You've asked something ... including possible to use VBA ... and now, You're thinking something else or how?
I'm not sure at all could You use it as You just noticed?
# What I need to do
... there seems to be two spinners still for some reason?
You can delete that smaller one.
Message ... it's one sample how to take care that user won't start to wondering - why there are nothing to show?
In this sample ... selected year (2025) and month (April) should be between dates which has written in that sheet.
... the latest data is ... May-2024 >> means ... select Year (2024) and Months can be April or May.
 

Attachments

  • proiecte_evidenta_in lucru.xlsb
    29.8 KB · Views: 6
Thank you! No, I didn't mention Drive because I had this request afterwards. Thanks for everything!

Alina_T

It would be good to read always all texts.
In some cases there could be something which You should know too.
I noticed that previous version has one line which should modify - delete Your previous version and use this one.
# Drive ... okay ....
Did You write that 'minor "Drive" detail' before?
You've asked something ... including possible to use VBA ... and now, You're thinking something else or how?
I'm not sure at all could You use it as You just noticed?
# What I need to do
... there seems to be two spinners still for some reason?
You can delete that smaller one.
Message ... it's one sample how to take care that user won't start to wondering - why there are nothing to show?
In this sample ... selected year (2025) and month (April) should be between dates which has written in that sheet.
... the latest data is ... May-2024 >> means ... select Year (2024) and Months can be April or May.
 
Can you tell me how you made the cells color? What did you use?

Alina_T

It would be good to read always all texts.
In some cases there could be something which You should know too.
I noticed that previous version has one line which should modify - delete Your previous version and use this one.
# Drive ... okay ....
Did You write that 'minor "Drive" detail' before?
You've asked something ... including possible to use VBA ... and now, You're thinking something else or how?
I'm not sure at all could You use it as You just noticed?
# What I need to do
... there seems to be two spinners still for some reason?
You can delete that smaller one.
Message ... it's one sample how to take care that user won't start to wondering - why there are nothing to show?
In this sample ... selected year (2025) and month (April) should be between dates which has written in that sheet.
... the latest data is ... May-2024 >> means ... select Year (2024) and Months can be April or May.
 
Back
Top