Conditional Formatting Issue

[RyanH]

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

 

[Nigel]

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

 

[Joel]

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]

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.

 

[Joel]

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]

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