when you use "with c", you don't need to use "c.value", just ".value".
you also don't need "next c", just "next".
you probably should qualify the sheet name, too. that way the code won't address
cells on the wrong sheet.
you can use the goto if you want, i use it sometimes, but it wasn't needed in
the code i posted. i don't think i'd use the number 1 as the goto location,
either.

dim ws as worksheet
set ws = worksheets("Sheet1")
Dim c As Range
For Each c In ws.Cells.Range("A1:A15") 'FP No.1
With c
If .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

--


Gary


"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?