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
__________________________________________________ ______

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
__________________________________________________ ______

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
__________________________________________________ ______

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
__________________________________________________ ______

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
__________________________________________________ ______

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
__________________________________________________ ______

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
__________________________________________________ ______