Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete text if red
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete text if red
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete text if red
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 "Anita" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete text if red
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 -- Rick (MVP - Excel) "Anita" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete text if red
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? "Mike H" wrote: 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 "Anita" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Row if text or empty | Excel Programming | |||
User Form Text Box - auto delete prompt text | Excel Programming | |||
delete all text that match | Excel Discussion (Misc queries) | |||
Delete Text Box | Excel Programming | |||
Delete text from cell containing text and numbers? | Excel Programming |