highlight date if date is in that week?

mholmes

New Member
Joined
Oct 30, 2002
Messages
16
I would like to do something that might be hard to do. I have a list of dates. I'd like to highlight them a special color if that date falls in this week, based on the current date.

Any ideas?


Thanks,

Marcus
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

mholmes

New Member
Joined
Oct 30, 2002
Messages
16
This might have to be done in VBA.. Can I just have some code that happens once the spreadsheet is opened?
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Ok this is a tricky one. There is a function called WEEKNUM which is in the Analysis Toolpak add-in (Choose Tools>Add-ins to activate it). The problem is, Conditional Formatting does not recognize WEEKNUM (at least in Excel 2000) so insert a column (B) next to your dates and enter =WEEKNUM(A1)=WEEKNUM(Today()) This will return TRUE or FALSE. Copy down as far as needed. Now select the dates and Choose Formatting>Conditional Formatting. Select the formula is option and Enter =B1=TRUE. Choose a format and click OK. Note:You can hide Column B

HTH
This message was edited by lenze on 2002-11-01 15:56
 

mholmes

New Member
Joined
Oct 30, 2002
Messages
16
Is there a way to do it in VBA? I have to send this to someone else - what if they don't have the add-in activated?
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

Yes, it can be done with VBA. I'll see what I can come up with. However, you can add this code in the ThisWorkBook module which will activate the add-in automatically.

Private Sub Workbook_Open()
If Application.AddIns("Analysis ToolPak").Installed = False Then Applications.AddIns("Analysis ToolPak").Installed = True
End If
End Sub
This message was edited by lenze on 2002-11-01 16:04
This message was edited by lenze on 2002-11-01 16:04
 

mholmes

New Member
Joined
Oct 30, 2002
Messages
16
Hmmm your code is telling me macros are not enabled. How do I enable macros/


Marcus
 

mholmes

New Member
Joined
Oct 30, 2002
Messages
16

ADVERTISEMENT

Hmmm for the conditional formatting though it's only looking in that one cell, B1.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Choose Tools>Macros>Security. Make sure your setting is not on high. Save file and reopen it. Select Enable macros when prompted
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Ok here's a way with conditional formatting that doesn't need the extra column.
Select the column of dates and choose Format>Conditional formatting. Select formula is and enter

=ROUND((A1+3)/7,0)=ROUND(TODAY()/7,0)

This assumes your week begins on Sunday Change the +3 to +4 for Monday, or +2 for Saturday
This message was edited by lenze on 2002-11-01 16:21
 

Forum statistics

Threads
1,148,277
Messages
5,745,818
Members
423,980
Latest member
zimza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top