Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cell with red text
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cell with red text
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 "Kara" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cell with red text
Font colorindex is a value in the 56 colour palette or
xlColorindexAutomatic. Also you are testing non-red cells, is this correct? -- HTH Bob Phillips "Kara" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to delete a row with a cell containing certain number or text | Excel Discussion (Misc queries) | |||
delete text in a cell using certian criteria | Excel Discussion (Misc queries) | |||
delete part of text from a cell | Excel Worksheet Functions | |||
Delete Spaces and Join Text in Cell | Excel Worksheet Functions | |||
Delete text from cell containing text and numbers? | Excel Programming |