Calendar 2
Here I have a calendar where every day has 2 rows. In this example let say
that today's date is 1st January. I need that cell B2 (Highlight) will be highlighted with any colour as I open the file and cell B3 (Details) remains empty where I can put my details. Any help please? Thanks in adnance. A B C D E F __________________________________________________ __ 1 JAN FEB MAR APR MAY __________________________________________________ __ 2 1 Highlight 3 Details __________________________________________________ ___ 4 2 5 __________________________________________________ ____ 6 3 7 __________________________________________________ ____ 8 4 9 __________________________________________________ ______ |
Calendar 2
You conditional formatting formula for cell C2 is:
=DATE(YEAR(TODAY()),TEXT(1&" "&C$1&" "&2000,"mm"),DAY($B2)) = TODAY() NOTE: TEXT(1&" "&C$1&" "&2000,"mm") converts JAN, FEB, MAR to 01. 02, 03 for the Date() function. It will also convert January, February, March. -- Steve "MAX" wrote in message ... Here I have a calendar where every day has 2 rows. In this example let say that today's date is 1st January. I need that cell B2 (Highlight) will be highlighted with any colour as I open the file and cell B3 (Details) remains empty where I can put my details. Any help please? Thanks in adnance. A B C D E F __________________________________________________ __ 1 JAN FEB MAR APR MAY __________________________________________________ __ 2 1 Highlight 3 Details __________________________________________________ ___ 4 2 5 __________________________________________________ ____ 6 3 7 __________________________________________________ ____ 8 4 9 __________________________________________________ ______ |
Calendar 2
The below code will highlight the current day cell as per your calendar
layout. Place this code in workbook open event. If you have the calendar in a default sheet you can add the sheet name as well. Launch VBE using Alt+F11. From the treeview double click 'This Workbook' and paste this code. Private Sub Workbook_Open() Range("B2").Resize(62, 31).Interior.ColorIndex = xlNone Cells(Day(Date) * 2, Month(Date) + 1).Interior.Color = vbYellow End Sub If this post helps click Yes --------------- Jacob Skaria "MAX" wrote: Here I have a calendar where every day has 2 rows. In this example let say that today's date is 1st January. I need that cell B2 (Highlight) will be highlighted with any colour as I open the file and cell B3 (Details) remains empty where I can put my details. Any help please? Thanks in adnance. A B C D E F __________________________________________________ __ 1 JAN FEB MAR APR MAY __________________________________________________ __ 2 1 Highlight 3 Details __________________________________________________ ___ 4 2 5 __________________________________________________ ____ 6 3 7 __________________________________________________ ____ 8 4 9 __________________________________________________ ______ |
Calendar 2
Hi Jacob If I insert a row at the top, does the code remains the same? Thanks in advance. "Jacob Skaria" wrote: The below code will highlight the current day cell as per your calendar layout. Place this code in workbook open event. If you have the calendar in a default sheet you can add the sheet name as well. Launch VBE using Alt+F11. From the treeview double click 'This Workbook' and paste this code. Private Sub Workbook_Open() Range("B2").Resize(62, 31).Interior.ColorIndex = xlNone Cells(Day(Date) * 2, Month(Date) + 1).Interior.Color = vbYellow End Sub If this post helps click Yes --------------- Jacob Skaria "MAX" wrote: Here I have a calendar where every day has 2 rows. In this example let say that today's date is 1st January. I need that cell B2 (Highlight) will be highlighted with any colour as I open the file and cell B3 (Details) remains empty where I can put my details. Any help please? Thanks in adnance. A B C D E F __________________________________________________ __ 1 JAN FEB MAR APR MAY __________________________________________________ __ 2 1 Highlight 3 Details __________________________________________________ ___ 4 2 5 __________________________________________________ ____ 6 3 7 __________________________________________________ ____ 8 4 9 __________________________________________________ ______ |
Calendar 2
With one row on top ...I have changed B2 to C2 and the day row +1. Try the
below and feedback Private Sub Workbook_Open() Range("C2").Resize(62, 31).Interior.ColorIndex = xlNone Cells(1+(Day(Date) * 2), Month(Date) + 1).Interior.Color = vbYellow End Sub If this post helps click Yes --------------- Jacob Skaria "MAX" wrote: Hi Jacob If I insert a row at the top, does the code remains the same? Thanks in advance. "Jacob Skaria" wrote: The below code will highlight the current day cell as per your calendar layout. Place this code in workbook open event. If you have the calendar in a default sheet you can add the sheet name as well. Launch VBE using Alt+F11. From the treeview double click 'This Workbook' and paste this code. Private Sub Workbook_Open() Range("B2").Resize(62, 31).Interior.ColorIndex = xlNone Cells(Day(Date) * 2, Month(Date) + 1).Interior.Color = vbYellow End Sub If this post helps click Yes --------------- Jacob Skaria "MAX" wrote: Here I have a calendar where every day has 2 rows. In this example let say that today's date is 1st January. I need that cell B2 (Highlight) will be highlighted with any colour as I open the file and cell B3 (Details) remains empty where I can put my details. Any help please? Thanks in adnance. A B C D E F __________________________________________________ __ 1 JAN FEB MAR APR MAY __________________________________________________ __ 2 1 Highlight 3 Details __________________________________________________ ___ 4 2 5 __________________________________________________ ____ 6 3 7 __________________________________________________ ____ 8 4 9 __________________________________________________ ______ |
Calendar 2
Opps
With one row on top ...I have changed B2 to B3 and the day row +1. Try the below and feedback Private Sub Workbook_Open() Range("B3").Resize(62, 31).Interior.ColorIndex = xlNone Cells(1+(Day(Date) * 2), Month(Date) + 1).Interior.Color = vbYellow End Sub -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: With one row on top ...I have changed B2 to C2 and the day row +1. Try the below and feedback Private Sub Workbook_Open() Range("C2").Resize(62, 31).Interior.ColorIndex = xlNone Cells(1+(Day(Date) * 2), Month(Date) + 1).Interior.Color = vbYellow End Sub If this post helps click Yes --------------- Jacob Skaria "MAX" wrote: Hi Jacob If I insert a row at the top, does the code remains the same? Thanks in advance. "Jacob Skaria" wrote: The below code will highlight the current day cell as per your calendar layout. Place this code in workbook open event. If you have the calendar in a default sheet you can add the sheet name as well. Launch VBE using Alt+F11. From the treeview double click 'This Workbook' and paste this code. Private Sub Workbook_Open() Range("B2").Resize(62, 31).Interior.ColorIndex = xlNone Cells(Day(Date) * 2, Month(Date) + 1).Interior.Color = vbYellow End Sub If this post helps click Yes --------------- Jacob Skaria "MAX" wrote: Here I have a calendar where every day has 2 rows. In this example let say that today's date is 1st January. I need that cell B2 (Highlight) will be highlighted with any colour as I open the file and cell B3 (Details) remains empty where I can put my details. Any help please? Thanks in adnance. A B C D E F __________________________________________________ __ 1 JAN FEB MAR APR MAY __________________________________________________ __ 2 1 Highlight 3 Details __________________________________________________ ___ 4 2 5 __________________________________________________ ____ 6 3 7 __________________________________________________ ____ 8 4 9 __________________________________________________ ______ |
Calendar 2
It works both with Range("C2") and Range("B3").
Thanks a lot. "Jacob Skaria" wrote: Opps With one row on top ...I have changed B2 to B3 and the day row +1. Try the below and feedback Private Sub Workbook_Open() Range("B3").Resize(62, 31).Interior.ColorIndex = xlNone Cells(1+(Day(Date) * 2), Month(Date) + 1).Interior.Color = vbYellow End Sub -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: With one row on top ...I have changed B2 to C2 and the day row +1. Try the below and feedback Private Sub Workbook_Open() Range("C2").Resize(62, 31).Interior.ColorIndex = xlNone Cells(1+(Day(Date) * 2), Month(Date) + 1).Interior.Color = vbYellow End Sub If this post helps click Yes --------------- Jacob Skaria "MAX" wrote: Hi Jacob If I insert a row at the top, does the code remains the same? Thanks in advance. "Jacob Skaria" wrote: The below code will highlight the current day cell as per your calendar layout. Place this code in workbook open event. If you have the calendar in a default sheet you can add the sheet name as well. Launch VBE using Alt+F11. From the treeview double click 'This Workbook' and paste this code. Private Sub Workbook_Open() Range("B2").Resize(62, 31).Interior.ColorIndex = xlNone Cells(Day(Date) * 2, Month(Date) + 1).Interior.Color = vbYellow End Sub If this post helps click Yes --------------- Jacob Skaria "MAX" wrote: Here I have a calendar where every day has 2 rows. In this example let say that today's date is 1st January. I need that cell B2 (Highlight) will be highlighted with any colour as I open the file and cell B3 (Details) remains empty where I can put my details. Any help please? Thanks in adnance. A B C D E F __________________________________________________ __ 1 JAN FEB MAR APR MAY __________________________________________________ __ 2 1 Highlight 3 Details __________________________________________________ ___ 4 2 5 __________________________________________________ ____ 6 3 7 __________________________________________________ ____ 8 4 9 __________________________________________________ ______ |
All times are GMT +1. The time now is 09:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com