Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / September 2007

Tip: Looking for answers? Try searching our database.

Making all past dates bold, looping

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Memento - 24 Sep 2007 19:36 GMT
Hello guys,

I'm am trying to lock all cell, and make cell content bold until the date of
today is encountered. the range contains dates in the default date format
(this is European style date formatting, thus dd/mm/yyyy). However, it keeps
on looping...

any suggestions about this code?

Sub Button_Click()
Dim currentDate As Date
currentDate = Date

For Each c In Range("D5:D369")
   Do Until c = currentDate
       c.Font.Color = RGB(0, 255, 0)
       c.Locked = True
       c.Font.Bold = True
   Loop
Next c
End Sub
JW - 24 Sep 2007 20:06 GMT
You have a Do Loop that is doing nothing but cycling through the same
cell over and over again.  No need for the Do Loop.  Try this:
Sub Button_Click()
   Dim currentDate As Date
   currentDate = Date
   For Each c In Range("D5:D369")
       If Not c >= currentDate Then
           c.Font.Color = RGB(0, 255, 0)
           c.Locked = True
           c.Font.Bold = True
       End If
   Next c
End Sub

> Hello guys,
>
[quoted text clipped - 17 lines]
> Next c
> End Sub
JW - 24 Sep 2007 20:10 GMT
And if you want to exit the sub once you have reached a cell that is
greater than or equal to the currentDate varaible, do something like
below.  The first code I posted will cycle through ALL cells in the
specified range.  Which one you use will depend on how your data is
structured.  If the dates are not in ascending order and there is a
possibility that there is "old" data after the first occurrence of >=
currentDate, then use the first code I posted.  Else, use this code.
Sub Button_Click()
   Dim currentDate As Date
   currentDate = Date
   For Each c In Range("D5:D18")
       If Not c >= currentDate Then
           c.Font.Color = RGB(0, 255, 0)
           c.Locked = True
           c.Font.Bold = True
       Else
           Exit For
       End If
   Next c
End Sub
> Hello guys,
>
[quoted text clipped - 17 lines]
> Next c
> End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.