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

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