This code is giving me fits and I can't see what the problem is. It is
probably obvious, but I'm so fustrated I can't see it. I am getting an error
below can someone tell me why? I am wanting to highlight every other row.
ERROR: Application Defined or Object Error

Sub ConditionalFormatting()

lngDeptLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
' highlight late dates red
With .Range("L5:L" & lngDeptLastRow)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue,
Operator:=xlLess, Formula1:="=TODAY()"
.FormatConditions(1).Interior.ColorIndex = 3
End With

' highlight everyother row light green
With .Range("A5:O" & lngDeptLastRow)
ERROR>> .FormatConditions.Add Type:=xlExpression,
Formula1:="=MOD(ROW(),2)=0"
.FormatConditions(2).Interior.ColorIndex = 35
End With

End Sub
--
Cheers,
Ryan

I tried it in xl2007 using code below and it worked fine!

Sub ConditionalFormatting()
Dim lngdeptlastrow As Long
With ActiveSheet
lngdeptlastrow = .Cells(Rows.Count, "A").End(xlUp).Row
' highlight late dates red
With .Range("L5:L" & lngdeptlastrow)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
Formula1:="=TODAY()"
.FormatConditions(1).Interior.ColorIndex = 3
End With

' highlight everyother row light green
With .Range("A5:O" & lngdeptlastrow)
.FormatConditions.Add Type:=xlExpression,
Formula1:="=MOD(ROW(),2)=0"
.FormatConditions(2).Interior.ColorIndex = 35
End With
End With
End Sub

--

Regards,
Nigel
(E-Mail Removed)



"RyanH" <(E-Mail Removed)> wrote in message
news:7BC03887-CE98-42CD-905F-(E-Mail Removed)...
> This code is giving me fits and I can't see what the problem is. It is
> probably obvious, but I'm so fustrated I can't see it. I am getting an
> error
> below can someone tell me why? I am wanting to highlight every other row.
> ERROR: Application Defined or Object Error
>
> Sub ConditionalFormatting()
>
> lngDeptLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> ' highlight late dates red
> With .Range("L5:L" & lngDeptLastRow)
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlCellValue,
> Operator:=xlLess, Formula1:="=TODAY()"
> .FormatConditions(1).Interior.ColorIndex = 3
> End With
>
> ' highlight everyother row light green
> With .Range("A5:O" & lngDeptLastRow)
> ERROR>> .FormatConditions.Add Type:=xlExpression,
> Formula1:="=MOD(ROW(),2)=0"
> .FormatConditions(2).Interior.ColorIndex = 35
> End With
>
> End Sub
> --
> Cheers,
> Ryan

There are a few problems with the code don't know why you didn't get more
errors

1) You have a period in front of a number of items without a WIT statment.
I added Activesheet
2) You can't apply formatcondition to a range of cells. I added a FOR loop
to handle the situation.
3) Your color index statement is wrong. Not sure what you want to do.
Changed it to just color the cell.


Sub ConditionalFormatting()
With ActiveSheet
lngDeptLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
' highlight late dates red
With .Range("L5:L" & lngDeptLastRow)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlLess, Formula1:="=TODAY()"
.FormatConditions(1).Interior.ColorIndex = 3
End With

' highlight everyother row light green

For Each cell In .Range("A5:O" & lngDeptLastRow)
cell.FormatConditions.Add Type:=xlExpression, _
Formula1:="=MOD(ROW(),2)=0"
cell.Interior.ColorIndex = 35
Next cell
End With
End Sub

"RyanH" wrote:

> This code is giving me fits and I can't see what the problem is. It is
> probably obvious, but I'm so fustrated I can't see it. I am getting an error
> below can someone tell me why? I am wanting to highlight every other row.
> ERROR: Application Defined or Object Error
>
> Sub ConditionalFormatting()
>
> lngDeptLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> ' highlight late dates red
> With .Range("L5:L" & lngDeptLastRow)
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlCellValue,
> Operator:=xlLess, Formula1:="=TODAY()"
> .FormatConditions(1).Interior.ColorIndex = 3
> End With
>
> ' highlight everyother row light green
> With .Range("A5:O" & lngDeptLastRow)
> ERROR>> .FormatConditions.Add Type:=xlExpression,
> Formula1:="=MOD(ROW(),2)=0"
> .FormatConditions(2).Interior.ColorIndex = 35
> End With
>
> End Sub
> --
> Cheers,
> Ryan

Thanks for the replys.

Nigel,
I got this code to work in 2007 also, but at work we have 2003 and it
doesn't seem to work. I tried using the 2003 recorder and it doesn't want to
work right, so I guess it is a 2003 issue.

Joel,
Sorry for the confusion on the code. You are right, this is a piece of the
entire procedure and I failed to mention the With ActiveSheet statement,
sorry about that. If I can't apply format conditions to a Range then why is
the part where I want to highlight a range of cells red it works?

My basic goal is this. In Col.L I have dates and if the dates are less than
todays date change the color background to red. Then, I want to change the
background color to a light green for everyother row for easy reading. For
some reason it worked on the macro recorder but not in my code.
--
Cheers,
Ryan


"Joel" wrote:

> There are a few problems with the code don't know why you didn't get more
> errors
>
> 1) You have a period in front of a number of items without a WIT statment.
> I added Activesheet
> 2) You can't apply formatcondition to a range of cells. I added a FOR loop
> to handle the situation.
> 3) Your color index statement is wrong. Not sure what you want to do.
> Changed it to just color the cell.
>
>
> Sub ConditionalFormatting()
> With ActiveSheet
> lngDeptLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> ' highlight late dates red
> With .Range("L5:L" & lngDeptLastRow)
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlCellValue, _
> Operator:=xlLess, Formula1:="=TODAY()"
> .FormatConditions(1).Interior.ColorIndex = 3
> End With
>
> ' highlight everyother row light green
>
> For Each cell In .Range("A5:O" & lngDeptLastRow)
> cell.FormatConditions.Add Type:=xlExpression, _
> Formula1:="=MOD(ROW(),2)=0"
> cell.Interior.ColorIndex = 35
> Next cell
> End With
> End Sub
>
> "RyanH" wrote:
>
> > This code is giving me fits and I can't see what the problem is. It is
> > probably obvious, but I'm so fustrated I can't see it. I am getting an error
> > below can someone tell me why? I am wanting to highlight every other row.
> > ERROR: Application Defined or Object Error
> >
> > Sub ConditionalFormatting()
> >
> > lngDeptLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> > ' highlight late dates red
> > With .Range("L5:L" & lngDeptLastRow)
> > .FormatConditions.Delete
> > .FormatConditions.Add Type:=xlCellValue,
> > Operator:=xlLess, Formula1:="=TODAY()"
> > .FormatConditions(1).Interior.ColorIndex = 3
> > End With
> >
> > ' highlight everyother row light green
> > With .Range("A5:O" & lngDeptLastRow)
> > ERROR>> .FormatConditions.Add Type:=xlExpression,
> > Formula1:="=MOD(ROW(),2)=0"
> > .FormatConditions(2).Interior.ColorIndex = 35
> > End With
> >
> > End Sub
> > --
> > Cheers,
> > Ryan

The VBA help is the same as your code. don't know why it doesn't work. I
also modified you code below to get the colorindex into the conditional
formating. I need another WITH

For Each cell In .Range("A5:O" & lngDeptLastRow)
With cell.FormatConditions.Add(Type:=xlExpression, _
Formula1:="=MOD(ROW(),2)=0")
.Interior.ColorIndex = 35
End With
Next cell


"RyanH" wrote:

> Thanks for the replys.
>
> Nigel,
> I got this code to work in 2007 also, but at work we have 2003 and it
> doesn't seem to work. I tried using the 2003 recorder and it doesn't want to
> work right, so I guess it is a 2003 issue.
>
> Joel,
> Sorry for the confusion on the code. You are right, this is a piece of the
> entire procedure and I failed to mention the With ActiveSheet statement,
> sorry about that. If I can't apply format conditions to a Range then why is
> the part where I want to highlight a range of cells red it works?
>
> My basic goal is this. In Col.L I have dates and if the dates are less than
> todays date change the color background to red. Then, I want to change the
> background color to a light green for everyother row for easy reading. For
> some reason it worked on the macro recorder but not in my code.
> --
> Cheers,
> Ryan
>
>
> "Joel" wrote:
>
> > There are a few problems with the code don't know why you didn't get more
> > errors
> >
> > 1) You have a period in front of a number of items without a WIT statment.
> > I added Activesheet
> > 2) You can't apply formatcondition to a range of cells. I added a FOR loop
> > to handle the situation.
> > 3) Your color index statement is wrong. Not sure what you want to do.
> > Changed it to just color the cell.
> >
> >
> > Sub ConditionalFormatting()
> > With ActiveSheet
> > lngDeptLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> > ' highlight late dates red
> > With .Range("L5:L" & lngDeptLastRow)
> > .FormatConditions.Delete
> > .FormatConditions.Add Type:=xlCellValue, _
> > Operator:=xlLess, Formula1:="=TODAY()"
> > .FormatConditions(1).Interior.ColorIndex = 3
> > End With
> >
> > ' highlight everyother row light green
> >
> > For Each cell In .Range("A5:O" & lngDeptLastRow)
> > cell.FormatConditions.Add Type:=xlExpression, _
> > Formula1:="=MOD(ROW(),2)=0"
> > cell.Interior.ColorIndex = 35
> > Next cell
> > End With
> > End Sub
> >
> > "RyanH" wrote:
> >
> > > This code is giving me fits and I can't see what the problem is. It is
> > > probably obvious, but I'm so fustrated I can't see it. I am getting an error
> > > below can someone tell me why? I am wanting to highlight every other row.
> > > ERROR: Application Defined or Object Error
> > >
> > > Sub ConditionalFormatting()
> > >
> > > lngDeptLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> > > ' highlight late dates red
> > > With .Range("L5:L" & lngDeptLastRow)
> > > .FormatConditions.Delete
> > > .FormatConditions.Add Type:=xlCellValue,
> > > Operator:=xlLess, Formula1:="=TODAY()"
> > > .FormatConditions(1).Interior.ColorIndex = 3
> > > End With
> > >
> > > ' highlight everyother row light green
> > > With .Range("A5:O" & lngDeptLastRow)
> > > ERROR>> .FormatConditions.Add Type:=xlExpression,
> > > Formula1:="=MOD(ROW(),2)=0"
> > > .FormatConditions(2).Interior.ColorIndex = 35
> > > End With
> > >
> > > End Sub
> > > --
> > > Cheers,
> > > Ryan

I'll just do a Loop for the Date Column. Here is what I did. This works
great! Thanks for your help!

If lngFinalDate < 5 Then
' delete all data from dept
.Rows("5:" & lngLastRow + 2).Delete Shift:=xlUp
Else
' delete all non active items from dept
.Rows(lngFinalDate + 1 & ":" & lngLastRow + 2).Delete
Shift:=xlUp

' find last row of updated schedule
lngDeptLastRow = .Cells(Rows.Count, "A").End(xlUp).Row

' highlight everyother row light green
With .Range("A5:O" & lngDeptLastRow)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression,
Formula1:="=MOD(ROW(),2)=0"
.FormatConditions(1).Interior.ColorIndex = 35
End With

' highlight late dates red
lngRowCounter = 5
Do Until .Cells(lngRowCounter, "L") >= Date Or
IsEmpty(.Cells(lngRowCounter, "L"))
With .Cells(lngRowCounter, "L")
.FormatConditions.Delete
.Interior.ColorIndex = 3
End With
lngRowCounter = lngRowCounter + 1
Loop
End If
--
Cheers,
Ryan


"Joel" wrote:

> The VBA help is the same as your code. don't know why it doesn't work. I
> also modified you code below to get the colorindex into the conditional
> formating. I need another WITH
>
> For Each cell In .Range("A5:O" & lngDeptLastRow)
> With cell.FormatConditions.Add(Type:=xlExpression, _
> Formula1:="=MOD(ROW(),2)=0")
> .Interior.ColorIndex = 35
> End With
> Next cell
>
>
> "RyanH" wrote:
>
> > Thanks for the replys.
> >
> > Nigel,
> > I got this code to work in 2007 also, but at work we have 2003 and it
> > doesn't seem to work. I tried using the 2003 recorder and it doesn't want to
> > work right, so I guess it is a 2003 issue.
> >
> > Joel,
> > Sorry for the confusion on the code. You are right, this is a piece of the
> > entire procedure and I failed to mention the With ActiveSheet statement,
> > sorry about that. If I can't apply format conditions to a Range then why is
> > the part where I want to highlight a range of cells red it works?
> >
> > My basic goal is this. In Col.L I have dates and if the dates are less than
> > todays date change the color background to red. Then, I want to change the
> > background color to a light green for everyother row for easy reading. For
> > some reason it worked on the macro recorder but not in my code.
> > --
> > Cheers,
> > Ryan
> >
> >
> > "Joel" wrote:
> >
> > > There are a few problems with the code don't know why you didn't get more
> > > errors
> > >
> > > 1) You have a period in front of a number of items without a WIT statment.
> > > I added Activesheet
> > > 2) You can't apply formatcondition to a range of cells. I added a FOR loop
> > > to handle the situation.
> > > 3) Your color index statement is wrong. Not sure what you want to do.
> > > Changed it to just color the cell.
> > >
> > >
> > > Sub ConditionalFormatting()
> > > With ActiveSheet
> > > lngDeptLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> > > ' highlight late dates red
> > > With .Range("L5:L" & lngDeptLastRow)
> > > .FormatConditions.Delete
> > > .FormatConditions.Add Type:=xlCellValue, _
> > > Operator:=xlLess, Formula1:="=TODAY()"
> > > .FormatConditions(1).Interior.ColorIndex = 3
> > > End With
> > >
> > > ' highlight everyother row light green
> > >
> > > For Each cell In .Range("A5:O" & lngDeptLastRow)
> > > cell.FormatConditions.Add Type:=xlExpression, _
> > > Formula1:="=MOD(ROW(),2)=0"
> > > cell.Interior.ColorIndex = 35
> > > Next cell
> > > End With
> > > End Sub
> > >
> > > "RyanH" wrote:
> > >
> > > > This code is giving me fits and I can't see what the problem is. It is
> > > > probably obvious, but I'm so fustrated I can't see it. I am getting an error
> > > > below can someone tell me why? I am wanting to highlight every other row.
> > > > ERROR: Application Defined or Object Error
> > > >
> > > > Sub ConditionalFormatting()
> > > >
> > > > lngDeptLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> > > > ' highlight late dates red
> > > > With .Range("L5:L" & lngDeptLastRow)
> > > > .FormatConditions.Delete
> > > > .FormatConditions.Add Type:=xlCellValue,
> > > > Operator:=xlLess, Formula1:="=TODAY()"
> > > > .FormatConditions(1).Interior.ColorIndex = 3
> > > > End With
> > > >
> > > > ' highlight everyother row light green
> > > > With .Range("A5:O" & lngDeptLastRow)
> > > > ERROR>> .FormatConditions.Add Type:=xlExpression,
> > > > Formula1:="=MOD(ROW(),2)=0"
> > > > .FormatConditions(2).Interior.ColorIndex = 35
> > > > End With
> > > >
> > > > End Sub
> > > > --
> > > > Cheers,
> > > > Ryan