Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-24-2018, 07:57 AM
David Matthews David Matthews is offline Macro to change an RGB table cell shading color to another RGB color Windows 7 64bit Macro to change an RGB table cell shading color to another RGB color Office 2016
Novice
Macro to change an RGB table cell shading color to another RGB color
 
Join Date: May 2018
Posts: 6
David Matthews is on a distinguished road
Default Macro to change an RGB table cell shading color to another RGB color


Hello, all,

I am looking for a macro to change an RGB table cell shading color to another RGB color.

Can this be done?

I searched and found nothing on the site.

Thanks so much in advance,

David
Reply With Quote
  #2  
Old 05-24-2018, 02:29 PM
David Matthews David Matthews is offline Macro to change an RGB table cell shading color to another RGB color Windows 7 64bit Macro to change an RGB table cell shading color to another RGB color Office 2016
Novice
Macro to change an RGB table cell shading color to another RGB color
 
Join Date: May 2018
Posts: 6
David Matthews is on a distinguished road
Default

This is all I've found so far. It does not work for me.

Sub test()
Dim rg As Range
Set rg = ActiveDocument.Range
With rg.Find
.Format = True
.Text = ""
.Font.Shading.BackgroundPatternColor = RGB(225, 225, 153)
.Replacement.Text = ""
While .Execute
rg.Font.Shading.BackgroundPatternColor = RGB(225, 225, 225)
rg.Collapse wdCollapseEnd
Wend
End With
End Sub
Reply With Quote
  #3  
Old 05-25-2018, 08:10 PM
jjfreedman jjfreedman is offline Macro to change an RGB table cell shading color to another RGB color Windows 10 Macro to change an RGB table cell shading color to another RGB color Office 2016
Advanced Beginner
 
Join Date: May 2012
Location: http://jay-freedman.info
Posts: 38
jjfreedman is on a distinguished road
Default

If the shading you want to change really is table cell shading, then (a) it is not .Font.Shading and (b) you can't use Range.Find to locate it. [There are three possible kinds of shading in a table cell, controlled by the dropdown in the Shading page of the Borders & Shading dialog: Text, Cell, and Table. In VBA each of them is addressed differently.]

This macro iterates through all the cells of each row of each table in the document and makes the change when it finds the proper color of Cell background shading.

Code:
Sub test()
    Dim tbl As Table
    Dim rw As Row
    Dim cel As Cell
    
    For Each tbl In ActiveDocument.Tables
        For Each rw In tbl.Rows
            For Each cel In rw.Cells
                If cel.Shading.BackgroundPatternColor = RGB(225, 225, 153) Then
                    cel.Shading.BackgroundPatternColor = RGB(225, 225, 225)
                End If
            Next cel
        Next rw
    Next tbl
End Sub
Reply With Quote
  #4  
Old 05-29-2018, 12:01 PM
David Matthews David Matthews is offline Macro to change an RGB table cell shading color to another RGB color Windows 7 64bit Macro to change an RGB table cell shading color to another RGB color Office 2016
Novice
Macro to change an RGB table cell shading color to another RGB color
 
Join Date: May 2018
Posts: 6
David Matthews is on a distinguished road
Default

Thank you. I know it wants to work, but, Dag nabbit, now I'm getting the attached Run-time error 5991.

I've researched it and can't figure out what code to put where.

What I've seen looks something like this:

Table table=Globals.ThisDocument.Tables[1];

Range range = table.Range;

for (int i = 1; i <= range.Cells.Count; i++)

{

if(range.Cells[i].RowIndex == table.Rows.Count)

range.Cells[i].Range.Text = range.Cells[i].RowIndex + ":" + range.Cells[i].ColumnIndex;

}


Any thoughts?

Thanks so much,

David
Attached Images
File Type: jpg 2018-05-29_13-58-08.jpg (15.8 KB, 14 views)
Reply With Quote
  #5  
Old 05-29-2018, 02:45 PM
jjfreedman jjfreedman is offline Macro to change an RGB table cell shading color to another RGB color Windows 10 Macro to change an RGB table cell shading color to another RGB color Office 2016
Advanced Beginner
 
Join Date: May 2012
Location: http://jay-freedman.info
Posts: 38
jjfreedman is on a distinguished road
Default

Word VBA always has refused to deal with rows in a table that contains vertically merged cells, or with columns in a table that contains horizontally merged cells. If your table has only vertically merged cells, you can run the For Each on the columns instead of the rows, like this:
Code:
Sub test()
    Dim tbl As Table
    Dim col As Column
    Dim cel As Cell
    
    For Each tbl In ActiveDocument.Tables
        For Each col In tbl.Columns
            For Each cel In col.Cells
                If cel.Shading.BackgroundPatternColor = RGB(225, 225, 153) Then
                    cel.Shading.BackgroundPatternColor = RGB(225, 225, 225)
                End If
            Next cel
        Next col
    Next tbl
End Sub
Another alternative is to go through all cells in the table's Range, like this:
Code:
Sub test()
    Dim tbl As Table
    Dim cel As Cell
    
    For Each tbl In ActiveDocument.Tables
        For Each cel In tbl.Range.Cells
            If cel.Shading.BackgroundPatternColor = RGB(225, 225, 153) Then
                cel.Shading.BackgroundPatternColor = RGB(225, 225, 225)
            End If
        Next cel
    Next tbl
End Sub
Either way, you could run into this problem: If the RGB(225, 225, 153) shading was applied after the cells were merged, then VBA will tell you that the BackgroundPatternColor of the merged cells is -1 instead of the actual RGB value, and the If statement will see the condition as False. That happens because only one of the cells that were merged together has the shading you expect, and the others are not shaded. The only way to avoid that is to unmerge, shade all of the cells that will be included, and then merge them again.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to change an RGB table cell shading color to another RGB color How to change the font color of specific text within a Word table cell epid011 Word Tables 2 05-15-2017 05:21 PM
Macro to change an RGB table cell shading color to another RGB color looking to change cell color by typing number into the cell ibs Word Tables 1 11-22-2016 06:31 PM
How to change the font color of cell values sky474 Excel 7 03-06-2014 09:15 AM
Change Cell Color Based On % Complete jrfoley3 Project 1 05-30-2013 05:24 AM
Macro to change an RGB table cell shading color to another RGB color Creating a Macro to change the shading of a cell Triscia Word VBA 3 01-30-2013 04:18 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 01:19 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft