Delete cell with red text

[Kara]

A customer of mine is working with an Excel spreadsheet and has created a
macro that worked in Excel 2000 but is not working in Excel 2003. He has
cells that are marked with red text. When he runs this macro he would like
the red cells to delete. Do you know why this code is not working? Also,
how do I add code to this macro to show the active cell rather than having
the curser at the top of the document?

' LateList Macro
'

Set LoopRange = ActiveSheet.Range("E3:E250")
For Each Cel In LoopRange
' MsgBox (Cel.Font.ColorIndex & ", " & Cel.Interior.ColorIndex)
If Cel.Font.ColorIndex <> 3 And Cel.Interior.ColorIndex = xlNone Then
Cel.EntireRow.Delete
End If
Next Cel

End Sub

 

[Tom Ogilvy]

LateList Macro
'

Set LoopRange = ActiveSheet.Range("E3:E250")
For Each Cel In LoopRange
' MsgBox (Cel.Font.ColorIndex & ", " & Cel.Interior.ColorIndex)
If Cel.Font.ColorIndex <> 3 And Cel.Interior.ColorIndex = xlNone
Then
Cel.EntireRow.Delete
End If
Next Cel

End Sub

the criteria in the code is that the colorindex of the Font is NOT red and
the interior of the cell is no fill. This would be the opposite of what you
describe as far as text color. Also, by looping forward and deleting, you
would miss adjacent rows that have red text.

LateList Macro
'
Dim LoopRange as Range, rng as Range, Cel as Range
Set LoopRange = ActiveSheet.Range("E3:E250")
For Each Cel In LoopRange
' MsgBox (Cel.Font.ColorIndex & ", " & Cel.Interior.ColorIndex)
cel.Activate
If Cel.Font.ColorIndex = 3 And Cel.Interior.ColorIndex = xlNone Then
if rng is nothing then
set rng = cel
else
set rng = Union(rng,cel)
End if
End If
Next Cel
if not rng is nothing then
rng.EntireRow.delete
End if
End sub
--
Regards,
Tom Ogilvy


End Sub

 

[Bob Phillips]

Font colorindex is a value in the 56 colour palette or
xlColorindexAutomatic. Also you are testing non-red cells, is this correct?