Delete text if red

[Anita]

We have a spreadsheet which has cells with numbers in red text and cells with
numbers in black text. We would like to be able to delete all the red text,
if that's possible? I don't know any programming myself but any help
appreciated. Version 2007.

Thanks

 

[Patrick Molloy]

sub DeleteRed()
dim cell as range
for each cell in activesheet.usedrange
if cell.value<>"" then
if cell.font.color = vbred then
cell.value =""
end if
end if
next
end sub

 

[Mike H]

Anita,

Your post is ambiguous in that in the last sentence you refer to deleting
red TEXT after have referred to deleting red NUMBERS.

I think what you want is to delete all numbers with red font. If thta
correct right click your sheet tab, view code and paste the code below in and
run it

Sub Red()
For Each c In ActiveSheet.UsedRange
If IsNumeric(c) And c.Font.Color = 255 Then
c.ClearContents
End If
Next
End Sub

Mike

 

[Rick Rothstein]

Here is a macro which minimizing the number of loops to only those cells
that actually contain an entry displayed in red ...

Sub DeleteCellsWithRedText()
Application.FindFormat.Font.ColorIndex = 3
On Error GoTo Done
Do
ActiveSheet.UsedRange.Find("*", SearchFormat:=True).Clear
Loop
Done:
End Sub

If that does not work, then try replacing the first code line with this one
instead...

Application.FindFormat.Font.Color = vbRed

 

[Patrick Molloy]

thats an interesting point. RED numbers are usually negative, while positive
numbers are black. This isn't to do with the actual cell's text color
property, it is to do with the FORMATTING of the cell. Quite different.
Anita: Do you mean that all NEGATIVE numbers should be deleted?