Delete row by color of font

[Lee]

I have 80 rows of data and and one of the columns contains the serial number
of the row (i.e. 1 through 80). The serial number font is normally black but
if the font is red, I need to find the row number to delete that row. Does
anyone know how to do that? It would be nice to do it without looping to
save time.
Thanks in advance,

 

[Rick Rothstein]

How did the font become red... manually colored or via a Conditional
Format?

 

[Lee]

It was manually colored not via conditional formating.
Regards,
Lee

 

[Rick Rothstein]

Assuming they were manually colored red...

Sorry, but you will need a loop, but the good news is the loop will only
execute as many times as there are serial numbers with red fonts (2 such
cells and the loop only iterates 2 times). This macro will do that for
manually colored text in Column A (change the "A" in the Columns property
call inside the loop to what ever column has your serial numbers)...

Sub DeleteRedFontRows()
Dim UserResponse As Variant
On Error GoTo NoRedFonts
Application.ScreenUpdating = False
Application.FindFormat.Font.ColorIndex = 3
Do
Columns("A").Find("*", SearchFormat:=True).EntireRow.Delete
Loop
NoRedFonts:
Application.ScreenUpdating = True
End Sub

 

[Lee]

Hmmm, that didn't work for me. Perhaps the problem is that I have a
different color of Red than ColorIndex 3. This works:

Sub DeleteRowsWithRedFont()
Dim row As Integer
For i = 2 To 81
If Range(Cells(i, 1), Cells(i, 1)).Font.Color = RGB(255, 0, 0) Then
Rows(i).Delete shift:=xlUp
End If
Next
End Sub

However, I like your method better since there is less looping. Do you know
how to relate RGB to ColorIndex colors?
Regards,
Lee

 

[Rick Rothstein]

Same code, different color property...

Sub DeleteRedFontRows()
Dim UserResponse As Variant
On Error GoTo NoRedFonts
Application.ScreenUpdating = False
Application.FindFormat.Font.Color = vbRed
Do
Columns("A").Find("*", SearchFormat:=True).EntireRow.Delete
Loop
NoRedFonts:
Application.ScreenUpdating = True
End Sub

Note the I used the VB built in constant vbRed instead of using an RGB
function call to produce the same value.

 

[Ron de Bruin]

Hi Lee

You can do it with this add-in
http://www.rondebruin.nl/easyfilter.htm

 

[Lee]

That worked perfectly. I'd really like to understand the code. Would you
mind explaining why it works? Is UserResponse a special type of object?
Regards,
Lee

 

[Lee]

Very interesting; thank you for the link.
Regards,
Lee

 

[Rick Rothstein]

First off, the Dim UserResponse As Variant does nothing, so you should
remove it... it was accidentally left in when I cannibalized a previous
posting of mine to create my answer to you (that previous posting asked the
user a question and the user's answer was placed in that variable). Now, for
the code. I have the On Error statement in there because the Do..Loop is
going to be run until an error occurs (which will occur when there is no
more red text to find). Turning the screen updating off will speed up the
code an hide all the individual deletions from the user (the error trap is
used to make sure screen updating is turned back on). The FindFormat
property is used in combination with the Find function and lets it do its
searching using cell formats; so, I use it to establish the Font.Color as a
search parameter. Next comes the Do..Loop which does the actual searching.
The first argument is the text it is searching for. Since we don't care what
the characters in the cell are, we just use the asterisk wildcard which tell
it to search for any text no matter what that text is. Because we are
looking for any text, it doesn't matter if we find it in all or part of the
text, so we can ignore setting the LookAt argument (whichever it is will not
matter). Since the search is being conducted only on Column A, we really
don't care if the search is by column or by row, so we can ignore setting
the SearchOrder argument as well. And, since the searching will continue
until there is no more red text in the column, we don't care where the
search starts at, so we can ignore setting the After argument. We also do
not care what setting the LookIn or MatchCase currently has either (again,
we are looking for any text that is red). Okay now for the SearchFormat
argument... setting it to True tells the Find function to use whatever is
set in the FindFormat property as part of its search criteria. All of this
taken together means the Find function will only locate red text in Column A
and then delete the entire row (we applied the EntireRow property to the
single cell range that the Find function returns and then applied the Delete
method to that entire row).

 

[Lee]

Excellent! Thank you for the detailed explanation.
Regards,
Lee