Hi. Here's an idea instead of using "GoTo."

Sub Demo()
Dim Cell As Range
For Each Cell In Cells.Range("A1:A15").Cells
With Cell
If .Value < vbNullString Then
If .Value < Now() Then
.Interior.Color = vbRed
.Offset(0, 1).ClearContents
Else
.Interior.Color = vbWhite
.Offset(0, 1).Value = 10
End If
End If
End With
Next Cell
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"NewGuy" wrote in message
...
Hey, guys. Thanks a WHOLE bunch. I've been messing with this for weeks and
I
couldn't figure it out.

I actually combined both of your solutions and it works perfectly:

Dim c As Range
For Each c In Cells.Range("A1:A15") 'FP No.1
With c
If c.Value = "" Then GoTo 1
If .Value < Now() Then
.Interior.Color = RGB(255, 0, 0)
.Offset(0, 1).ClearContents
Else
.Interior.Color = RGB(255, 255, 255)
.Offset(0, 1).Value = "10"
End If
End With
1
Next c

Thanks again. I really appreciate it.

- Ethan

"Gary Keramidas" wrote:

sorry, i thought someone posted a solution for you to try. try this

Sub test()
Dim c As Range
For Each c In Cells.Range("A1:A15") 'FP No.1
With c
If .Value = "" Then
' do nothing
ElseIf .Value < Now() Then
.Interior.Color = RGB(255, 0, 0)
.Offset(0, 1).ClearContents
Else
.Interior.Color = RGB(255, 255, 255)
.Offset(0, 1).Value = "10"
End If
End With
Next c
End Sub

--


Gary


"Alan" wrote in message
...
I totaly agree, but I don't see a way passed blank cells, as requested,
using
your method, which, might I say, is much clearner.

Dim c As Range
For Each c In Cells.Range("A1:A15") 'FP No.1
If ActiveCell = "" Then GoTo 1
With c
If .Value < Now() Then
.Interior.Color = RGB(255, 0, 0)
.Offset(0, 1).ClearContents
Else
.Interior.Color = RGB(255, 255, 255)
.Offset(0, 1).Value = "10"
End If
End With
1
Next c
End Sub

Alan


"The only dumb question is a question left unasked."


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
you can use the solutions others provided, but i'd change the code to
something like this because selects and activates are rarely needed.


Dim c As Range
For Each c In Cells.Range("A1:A15") 'FP No.1
With c
If .Value < Now() Then
.Interior.Color = RGB(255, 0, 0)
.Offset(0, 1).ClearContents
Else
.Interior.Color = RGB(255, 255, 255)
.Offset(0, 1).Value = "10"
End If
End With
Next c
End Sub


--


Gary


"NewGuy" wrote in message
...
Dear Bernie,
Thanks for getting back to me. This is the formula I currently have
(VBE):
For Each C In Cells.Range("A1:A15") 'FP No.1
If C.Value < Now() Then
C.Select
Selection.Activate
ActiveCell.Interior.Color = RGB(255, 0, 0)
ActiveCell.Offset(0, 1).ClearContents
Else: C.Select
Selection.Activate
ActiveCell.Interior.Color = RGB(255, 255, 255)
ActiveCell.Offset(0, 1).Value = "10"
End If
Next C

This does everything I need it to do except for skip the blank cells.
I've
tried using the IgnoreBlank Property but it's not working out for me.

"Bernie Deitrick" wrote:

Post the formula that you are using?

I would suggest using conditional formatting. A CF formula of
something
like this

=AND(A1<"",A1<NOW())

applied when A1 is the activecell will turn any non-blank cells with
a date
that has passed to
whatever color you specify in the formatting. Just apply it to
dates, or
your other cells may turn
colors too....

HTH,
Bernie
MS Excel MVP


"NewGuy" wrote in message
...
I've got a worksheet that lists our clients and the services we
provide.
The
services we provide are done on a monthly or quarterly basis and
each
service
is due by a certain date. I've created a formula in VBE that turns
any
cell
that is past the due date red.
However, there are certain services that some clients do not
recieve and
so
do not have a due date, and therefore should not be included in
this
formula.
I've tried using multiple ranges, and not including those cells in
the
formula, but this a) creates a very long code for a simple
function and
b) if
I add a row (new client) onto the sheet, It messes up all of my
ranges
and I
have to re-do them every time I add or delete a client.
I've been messing with this for weeks and I can't figure out an
easier
way.
Any suggestions?