Vba: Selecting table cells by fill colour

effimera · #1 10-24-2021, 12:40 PM

Hi there,

I was wondering if it is possible to create a macro that selects table cells with a specific fill colour.

In MS Word, I've got a table with 4 fill colours. The table extends over 300 pages, and I would need to select only two colours (if one at a time, that's OK, but the quicker the better), then to delete them.

I tried an alternative procedure by using Excel:
copy & paste > apply Filter by Colour > delete
However, this method is unsatisfactory in that Excel automatically sets the List and thus some unique numbers as General, resulting in a recalculation with entirely different numbers/codes (such as 8,15381E+11 instead of 815381020338 or 5.V instead of 5/5).

Therefore, I'd much prefer to use a macro in Word, if that's even possible.

Thanks a lot!

Guessed · #2 10-24-2021, 02:45 PM

You need to post a sample document. Colours can be described in different ways (theme or RGB) so we need to see what you are looking at.

When you say your want cells deleted - do you mean delete the cell or remove the contents of the cell? If removing contents, do you want the cell recoloured afterwards.

effimera · #3 10-25-2021, 12:42 AM

I've added the Word file with a sample table.

What I meant was deleting the cells completely, i.e. not only the content but also the cell line.

So for example, I need to delete all grey and green cells, with the orange and white ones being untouched.

Thank you in advance.

gmayor · #4 10-25-2021, 01:26 AM

Try the following

Code:

Sub Macro1()
Dim i As Long
Dim oTable As Table
    For Each oTable In ActiveDocument.Tables
        For i = oTable.Rows.Count To 1 Step -1
            If oTable.Cell(i, 1).Shading.BackgroundPatternColor = &HCCFF00 Or _
               oTable.Cell(i, 1).Shading.BackgroundPatternColor = &HB2B2B2 Then
                oTable.Rows(i).Delete
            End If
        Next i
        DoEvents
    Next oTable
    Set oTable = Nothing
End Sub

effimera · #5 10-25-2021, 12:26 PM

It works perfectly!

Thank you so much

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
New table line has some cells fill colour change	trevorc	Excel	2	06-30-2021 12:27 PM
Text box fill colour changes when pasted to new file	ParishPete	Publisher	0	06-21-2019 12:47 AM
In Excel 2007-After Selecting Visibe Cells-How do I "Copy to Visible cells" Only	mag	Excel	0	10-28-2012 08:04 PM
How to fill a font colour to a particular data appear so many times in a column?	PRADEEPB270	Excel	0	03-22-2012 02:04 AM
Accessibility Display & Colour Fill in Shapes	thorn	Office	0	08-11-2011 07:58 AM