Macro to delete text from cells with specific format

Soenke · #1 08-31-2016, 08:04 AM

Hi
my first post here.
I am trying to create a macro that can delete the text out of all blue cells in my forms.
I used an existing macro and modified it but cant seem to get it work.
The command "myCell.ClearContents" doesn't want to work.
What am I doing wrong?

Sub DeleteTextFromBlueCells()

If Not Selection.Information(wdWithInTable) Then
MsgBox "Place the cursor in a table cell with a colored pattern."
Exit Sub
End If

Dim myRange As Range
CurrentTexture = Selection.Range.Cells(1).Shading.Texture
CurrentForegroundPatternColor = Selection.Range.Cells(1).Shading.ForegroundPattern Color
CurrentBackgroundPatternColor = Selection.Range.Cells(1).Shading.BackgroundPattern Color

For Each myTable In ActiveDocument.Tables
Set myRange = myTable.Range
For Each myCell In myRange.Cells
If myCell.Shading.Texture = CurrentTexture _
And myCell.Shading.ForegroundPatternColor = CurrentForegroundPatternColor _
And myCell.Shading.BackgroundPatternColor = CurrentBackgroundPatternColor Then
myCell.ClearContents

End If
Next myCell
Next myTable

End Sub

macropod · #2 09-01-2016, 03:11 AM

The answer is quite simple: ClearContents is an Excel cell-clearing method, not a Word cell-clearing method. In Word, ClearContents is only a method of the ChartArea object.

Soenke · #3 09-01-2016, 04:45 AM

Thank you Paul. So how do I get it right?

macropod · #4 09-01-2016, 06:13 AM

You could use code like:

Code:

Sub DeleteTextFromColoredCells()
Application.ScreenUpdating = False
Dim myTable As Table, myCell As Cell
Dim CurrentTexture As Long
Dim CurrentForegroundPatternColor As Long
Dim CurrentBackgroundPatternColor As Long
If Not Selection.Information(wdWithInTable) Then
  MsgBox "Place the cursor in a table cell with a colored pattern."
  Exit Sub
End If
With Selection.Range.Cells(1).Shading
  CurrentTexture = .Texture
  CurrentForegroundPatternColor = .ForegroundPatternColor
  CurrentBackgroundPatternColor = .BackgroundPatternColor
End With
For Each myTable In ActiveDocument.Tables
  For Each myCell In myTable.Range.Cells
    With myCell
      If .Shading.Texture = CurrentTexture Then
        If .Shading.ForegroundPatternColor = CurrentForegroundPatternColor Then
          If .Shading.BackgroundPatternColor = CurrentBackgroundPatternColor Then
            .Range.Text = ""
          End If
        End If
      End If
    End With
  Next myCell
Next myTable
Application.ScreenUpdating = True
End Sub

PS: When posting code please use the code tags, inserted via the # button on the posting menu.

Soenke · #5 09-01-2016, 08:55 AM

WOW! this works so well!
Thank you so much!!!

All the best
Sönke

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Delete specific text	Jackson	Word VBA	16	12-06-2019 08:24 PM
Macro to delete text in specific styles	ljd108	Word VBA	14	01-22-2019 01:22 PM
Delete cell content that has a specific format	Daniel Arbeit	Excel Programming	1	06-20-2016 03:02 PM
Delete specific mass text on document	JadeRisley	Word	4	07-17-2013 11:11 PM
Macro to delete rows with all empty cells	ubns	Excel Programming	2	08-14-2012 02:01 AM