Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-19-2014, 02:46 AM
QA_Compliance_Advisor QA_Compliance_Advisor is offline Word VBA Find Table Text Shading Colour and replace with another Windows 7 32bit Word VBA Find Table Text Shading Colour and replace with another Office 2010 32bit
Advanced Beginner
Word VBA Find Table Text Shading Colour and replace with another
 
Join Date: Jul 2014
Posts: 44
QA_Compliance_Advisor is on a distinguished road
Default Word VBA Find Table Text Shading Colour and replace with another


I need to be able to find and replace Table Text Shading colour which maybe in more than one row and column.

Does anyone have knowledge how to do this.

It will also have to be able to work with the rest of a code that has been written to be able to to multuple word docuements (code attached).
Reply With Quote
  #2  
Old 09-19-2014, 03:35 AM
QA_Compliance_Advisor QA_Compliance_Advisor is offline Word VBA Find Table Text Shading Colour and replace with another Windows 7 32bit Word VBA Find Table Text Shading Colour and replace with another Office 2010 32bit
Advanced Beginner
Word VBA Find Table Text Shading Colour and replace with another
 
Join Date: Jul 2014
Posts: 44
QA_Compliance_Advisor is on a distinguished road
Default

I have this code which seems to only change the first cell.


Code:
Function ChangeLogo(wdDoc)
Dim ocell As Cell
For Each ocell In wdDoc.Range.Cells
  With ocell.Shading
    If .BackgroundPatternColor = RGB(51, 51, 153) Then
    .BackgroundPatternColor = RGB(12, 71, 157)
    .ForegroundPatternColor = wdColorWhite
    End If
  End With
Next
End Function
Reply With Quote
  #3  
Old 09-19-2014, 04:38 AM
gmayor's Avatar
gmayor gmayor is offline Word VBA Find Table Text Shading Colour and replace with another Windows 7 64bit Word VBA Find Table Text Shading Colour and replace with another Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

You need to identify the table the cells are in e.g.

Code:
Function ChangeLogo(wdDoc)
Dim oTable As Table
Dim ocell As Cell
    Set oTable = wdDoc.Tables(1)
    For Each ocell In oTable.Range.Cells
        With ocell.Shading
            If .BackgroundPatternColor = RGB(51, 51, 153) Then
                .BackgroundPatternColor = RGB(12, 71, 157)
                .ForegroundPatternColor = wdColorWhite
            End If
        End With
    Next ocell
End Function
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #4  
Old 09-19-2014, 04:42 AM
QA_Compliance_Advisor QA_Compliance_Advisor is offline Word VBA Find Table Text Shading Colour and replace with another Windows 7 32bit Word VBA Find Table Text Shading Colour and replace with another Office 2010 32bit
Advanced Beginner
Word VBA Find Table Text Shading Colour and replace with another
 
Join Date: Jul 2014
Posts: 44
QA_Compliance_Advisor is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
You need to identify the table the cells are in e.g.

Code:
Function ChangeLogo(wdDoc)
Dim oTable As Table
Dim ocell As Cell
    Set oTable = wdDoc.Tables(1)
    For Each ocell In oTable.Range.Cells
        With ocell.Shading
            If .BackgroundPatternColor = RGB(51, 51, 153) Then
                .BackgroundPatternColor = RGB(12, 71, 157)
                .ForegroundPatternColor = wdColorWhite
            End If
        End With
    Next ocell
End Function

I did it this way? any thoughts?

Code:
Function ChangeLogo(wdDoc)
Dim oCell As Cell, oTbl As Table, oRow As Row, oRng As Range
Dim i As Long
For Each oTbl In wdDoc.Tables
  For Each oRow In oTbl.Rows
    For Each oCell In oRow.Cells
      If oCell.Shading.BackgroundPatternColor = RGB(51, 51, 153) Then
      oCell.Shading.BackgroundPatternColor = RGB(12, 71, 157)
      End If
    Next
   Next
 Next
  
End Function
having troubles changing the text colour in the cell. Any suggestions?
Reply With Quote
  #5  
Old 09-19-2014, 05:00 AM
gmayor's Avatar
gmayor gmayor is offline Word VBA Find Table Text Shading Colour and replace with another Windows 7 64bit Word VBA Find Table Text Shading Colour and replace with another Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

If the table is in the body of the document, and if the background colour is as you indicate (Use ColorCop to sample the colour if necessary), then that should work. It changes the colour from a dark blue to a slightly lighter blue.

If the table is not in the body then you will have to tell the macro in which story range to look for the table(s).
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #6  
Old 09-19-2014, 05:21 AM
QA_Compliance_Advisor QA_Compliance_Advisor is offline Word VBA Find Table Text Shading Colour and replace with another Windows 7 32bit Word VBA Find Table Text Shading Colour and replace with another Office 2010 32bit
Advanced Beginner
Word VBA Find Table Text Shading Colour and replace with another
 
Join Date: Jul 2014
Posts: 44
QA_Compliance_Advisor is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
If the table is in the body of the document, and if the background colour is as you indicate (Use ColorCop to sample the colour if necessary), then that should work. It changes the colour from a dark blue to a slightly lighter blue.

If the table is not in the body then you will have to tell the macro in which story range to look for the table(s).

all this is in te body text, none are in headers or footers or anywhere else.

and still the text colour won't change.
Reply With Quote
  #7  
Old 09-19-2014, 05:42 AM
QA_Compliance_Advisor QA_Compliance_Advisor is offline Word VBA Find Table Text Shading Colour and replace with another Windows 7 32bit Word VBA Find Table Text Shading Colour and replace with another Office 2010 32bit
Advanced Beginner
Word VBA Find Table Text Shading Colour and replace with another
 
Join Date: Jul 2014
Posts: 44
QA_Compliance_Advisor is on a distinguished road
Default

Quote:
Originally Posted by QA_Compliance_Advisor View Post
I did it this way? any thoughts?

Code:
Function ChangeLogo(wdDoc)
Dim oCell As Cell, oTbl As Table, oRow As Row, oRng As Range
Dim i As Long
For Each oTbl In wdDoc.Tables
  For Each oRow In oTbl.Rows
    For Each oCell In oRow.Cells
      If oCell.Shading.BackgroundPatternColor = RGB(51, 51, 153) Then
      oCell.Shading.BackgroundPatternColor = RGB(12, 71, 157)
      End If
    Next
   Next
 Next
 
End Function
having troubles changing the text colour in the cell. Any suggestions?
how would I deal with merged cells?
Reply With Quote
  #8  
Old 09-19-2014, 05:48 AM
gmaxey gmaxey is offline Word VBA Find Table Text Shading Colour and replace with another Windows 7 32bit Word VBA Find Table Text Shading Colour and replace with another Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

The code works. Run this code in a new document to illustrate.

Code:
Sub Color()
With ActiveDocument
  .Tables.Add Selection.Range, 5, 5
  .Tables(1).Cell(2, 3).Shading.BackgroundPatternColor = RGB(51, 51, 153)
  .Tables(1).Cell(3, 2).Shading.BackgroundPatternColor = RGB(51, 51, 153)
  ChangeLogo ActiveDocument
End With
End Sub
 Function ChangeLogo(wdDoc)
Dim oCell As Cell, oTbl As Table, oRow As Row
Dim i As Long
For Each oTbl In wdDoc.Tables
  For Each oRow In oTbl.Rows
    For Each oCell In oRow.Cells
      If oCell.Shading.BackgroundPatternColor = RGB(51, 51, 153) Then
      oCell.Shading.BackgroundPatternColor = RGB(12, 71, 157)
      End If
    Next
   Next
 Next
  
End Function
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #9  
Old 09-19-2014, 06:36 AM
gmayor's Avatar
gmayor gmayor is offline Word VBA Find Table Text Shading Colour and replace with another Windows 7 64bit Word VBA Find Table Text Shading Colour and replace with another Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Quote:
Originally Posted by QA_Compliance_Advisor View Post
and still the text colour won't change.
There is no command in your macro to set the font colour. If you want white text add

oCell.Range.Font.ColorIndex = wdWhite
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #10  
Old 09-19-2014, 06:46 AM
QA_Compliance_Advisor QA_Compliance_Advisor is offline Word VBA Find Table Text Shading Colour and replace with another Windows 7 32bit Word VBA Find Table Text Shading Colour and replace with another Office 2010 32bit
Advanced Beginner
Word VBA Find Table Text Shading Colour and replace with another
 
Join Date: Jul 2014
Posts: 44
QA_Compliance_Advisor is on a distinguished road
Default

Quote:
Originally Posted by gmaxey View Post
The code works. Run this code in a new document to illustrate.
Could you explain why you did what you did - just to give me a better understanding of whats going on with the new code.

Quote:
There is no command in your macro to set the font colour. If you want white text add

oCell.Range.Font.ColorIndex = wdWhite
I tried the code but it never worked - however, I will give it a try again.
Reply With Quote
  #11  
Old 09-19-2014, 08:36 AM
gmaxey gmaxey is offline Word VBA Find Table Text Shading Colour and replace with another Windows 7 32bit Word VBA Find Table Text Shading Colour and replace with another Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

I did what I did just to show you that if you isolate the code for evaluating and changing cell shading that it does work. I asked you to run the code I posted in a new document.

That code
1. Created a table
2. Shaded some of the cells with a certain color
3. Then called a function and passed the activedocument as an argument
4. The function evaluated each cell and if the cell shading match that applied in step 2, it changed it to a new color.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Word VBA Find Table Text Shading Colour and replace with another Macro to find text and replace with form field containing that text iiiiifffff Word VBA 16 06-04-2016 01:47 AM
Find and Replace multiple table columns smakdown61 Word 1 08-28-2014 09:24 PM
Word VBA Find Table Text Shading Colour and replace with another Word VBA Macro to Find and Replace based on the Alt Text of an Image bennymc Word VBA 1 01-27-2014 04:23 PM
Find - Replace Macro using a table list mdw Word 0 08-01-2013 04:36 PM
Word VBA Find Table Text Shading Colour and replace with another Bad view when using Find and Find & Replace - Word places found string on top line paulkaye Word 4 12-06-2011 11:05 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:28 PM.


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