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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
This might have to be done in VBA.. Can I just have some code that happens once the spreadsheet is opened?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Choose Tools>Macros>Security. Make sure your setting is not on high. Save file and reopen it. Select Enable macros when prompted
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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
Back
Top