Making all past dates bold, looping

M

Memento

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
 
J

JW

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
 
J

JW

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top