Delete text if red

  • Thread starter Thread starter Anita
  • Start date Start date
A

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
 
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
 
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
 
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
 
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?
 
Back
Top