• 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.

Add text in highlighted cells through conditional formatting

Akshay Handoo

New Member
Dear Excel experts,

I have created a horizontal 1 year calendar with names of all employees in row B3: B40. When leaves are applied in 'Leave tracker' sheet, it will highlight the same dates marked in 'Leave cal' sheet depending on dates mentioned.

Please help me without any VBA for a conditional formatting formula which can

1. Write text in sheet dynamically from "H3" leave tracker sheet in the middle of the highlighted dates of the 'Leave cal' sheet. Text to appear over the highlighted color once in the center of the highlight color.
 

Attachments

  • Personal Staff tracker 2022.xlsx
    179.2 KB · Views: 8
It cannot be done without VBA.

The text can be displayed using formulas but you would have to put a formula in every cell C3:NC40, and your cells are too small to display the message in one cell. So it can't be done with formulas.

Using VBA you can leave all the cells blank and then populate only the necessary cells with the message, which will extend across as many cells needed to display the entire message.

I also notice that C5:NC40 are populated with dates, which is unnecessary.
 
Thanks for your observation and reply. I was keen on doing it without VBA as I don’t understand it.
It would be helpful if you can show solution with a VBA code.
 
Here is a VBA solution. However, it is not in the center of the highlighted section, it's at the beginning. It is possible to put it in the middle but I have limited time to work on unpaid projects.

A change to a start date, end date, or status will add that comment to the calendar.

I have not worked out removing comments if something changes.
 

Attachments

  • Akshay Handoo=Personal Staff tracker 2022.xlsm
    164.5 KB · Views: 11
Hey thanks. Its working well.
Doesn't matter if its not in center, as it was only for aesthetic reason. Your solution is solving the purpose and is more than enough. Really helpful of you 6StringJazzer.
 
Glad to help!

The strategy for centering the text would be to format all of the highlighted cells as "horizontal center across selection" This would involve the code finding the cells for start and and dates and then applying that format.
 
Back
Top