Skipping cells in a formula

[Guest]

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?

 

[Bernie Deitrick]

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

 

[Guest]

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.

 

[Gary Keramidas]

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

 

[Alan]

For Each C In Cells.Range("A1:A15") 'FP No.1
If C.Value ="" GoTo 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
1
Next C


Alan


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

 

[Gary Keramidas]

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

 

[Alan]

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]

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

 

[Guest]

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

 

[Dana DeLouis]

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

 

[Dana DeLouis]

Hi Gary. Thanks. Didn't see it. I'm using Outlook Express, and for the
last year or so, I seem to be missing many posts. Others have reported the
same problem. I still don't have a solution.
In addition, I'll often see a subject "Re: xxx", but not the original posted
question.
I've experimented with other news readers, and they seem to retrieve all the
posts just fine.
It looks to me like an Outlook Express problem.
Very frustrating !! ;>(
--
Dana DeLouis

already provided the op with that and they chose not to use it.

<snip>

 

[Gary Keramidas]

already provided the op with that and they chose not to use it.

 

[Gary Keramidas]

i use oe, too. i see that once in a while, but not as much as you say you're
seeing it. it's usually a server sync problem when that happens.