[Solved] Highlight a Cell in a table based on today

[piterlebel]

Hello,

I am looking for some help to highlight a cell in a date table based on today's date where I have day 1 through 31 in Row 1 so the 1st is B2, the 2nd is C2, the 3rd is D2 etc. until the 31st in AF2 and the month are in Column A so January is in A3, February A4 etc. until December in A13.

example below. if we were the 2nd of February today.

	1	2	3
January
February		HIGHLITED CELL
Mars

thank you

 

[Lutfor Rahman Shimanto]

Hello,

I am looking for some help to highlight a cell in a date table based on today's date where I have day 1 through 31 in Row 1 so the 1st is B2, the 2nd is C2, the 3rd is D2 etc. until the 31st in AF2 and the month are in Column A so January is in A3, February A4 etc. until December in A13.

example below. if we were the 2nd of February today.

	1	2	3
January
February		HIGHLITED CELL
Mars

thank you

Hello Piterlebel,
Thanks for reaching out and sharing your difficulties with us. By analyzing your problem, we understand that you need help with a formula to fill all dates using the months from First Column starting from A3 to till A14 and the numbers from First Row starting from B2 to AL2. We can solve the issues by combining the DATE, YEAR, MONTH, TODAY and DATEVALUE Functions. Latterly, we applied the Conditional Formatting in the desired range. I have attached the solved workbook; some necessary steps are mentioned to complete the task.

Formula Applied in Cells:
=DATE(YEAR(TODAY()),MONTH(DATEVALUE("1 "&$A3&" 2023")),B$2)

Formula Used for Formatting:
=B3=TODAY()

Steps:

• Select the B3 cell, insert the mentioned formula and hit Enter.
• Next, hold the AutoFill Handle icon and drag it to AF3.
• After that, utilize the AutoFill Handle again and drag it to AF14.
• Now, choose the B3:AF14 range and click on Conditional Formatting from the Styles group.
• Later, pick the New Rule option and apply the formula mentioned for formatting.
• Finally, format your desired formatting and hit OK several times for the intended result.

​

Please get in touch with us if you have any additional Excel-related questions or problems. We are here to assist you with any Excel-related concerns you may encounter.

Regards
Lutfor Rahman Shimanto

 

[piterlebel]

Hello,

Thank you for the reply, it is pretty close to what I need but not quite, I would want today's date to be highlighted, but i do not want the whole table to be filled with dates. I'm looking for a formula that will highlight a cell based on the days of Row 2 and the months of Columns A and highlight where that intersect

 

[Lutfor Rahman Shimanto]

Hello,

Thank you for the reply, it is pretty close to what I need but not quite, I would want today's date to be highlighted, but i do not want the whole table to be filled with dates. I'm looking for a formula that will highlight a cell based on the days of Row 2 and the months of Columns A and highlight where that intersect

Dear Piterlebel,

Thank you for clarifying your requirements. I'm glad my previous response was close to what you were looking for.

Regarding your recent message, I understand that you want to highlight a cell in the table based on the days of Row 2 and the months of Columns A without filling the entire table with dates. To achieve this, you can use the following formula for formatting:
=DATE(YEAR(TODAY()),MONTH(DATEVALUE("1 "&$A3&" 2023")),B$2)=TODAY()

​

I have attached the solved workbook this time as well for better understanding. Hopefully, this time the solution will touch your requirement. If you have any further concerns or inquiries concerning Excel, please don't hesitate to contact us.

Regards
Lutfor Rahman Shimanto