Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-07-2022, 08:29 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help, How to do a Find in a Table, then Shade color? Windows 10 Help, How to do a Find in a Table, then Shade color? Office 2019
Competent Performer
Help, How to do a Find in a Table, then Shade color?
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 190
Cendrinne is on a distinguished road
Default Help, How to do a Find in a Table, then Shade color?

Hello Pros,
I've been searching long and hard how to do this simple task, yet, I can't figure it out. Search days on the web or trial and error, I still can't find a simple solution.

I wish to do a Search for a "Word / Symbole / Values / Texts", in a Table, with many rows and a couple of Columns. I get those very often.



I have already created a macro to ID texts or values, by: Highlight them + put Fonts in Bold + in Color (some are in Red, others in Green).

Now to be certain, I don't miss a cell with those values, if I scroll too quickly, I do miss one or more, I wish to have those Cells with the Highlighted, Bold, and Color "Word / Symbole / Values / Texts", with a background Shade of this color, if possible = (.Shading.BackgroundPatternColor = -654246093). It's supposed to be a paler shade of Orange (very very light), so not agressif as a color, but enough to get my attention.

***By the way, it can have more than one of the same value in the cell, It doesn't matter, as soon as it finds one of those value, have the whole cell in the color of shade that I gave the decimal value.

Any insights?

I could show you my many fail attemps of my macros, but I think I would confused people. May as well get a guidance how to accomplish this task.

Unless it's not doable?

Many thanks in advance, I am soooooooooooooo appreciative in advance for your help

Happy New Year pro's.

Cendrinne
Reply With Quote
  #2  
Old 01-07-2022, 11:39 PM
gmayor's Avatar
gmayor gmayor is offline Help, How to do a Find in a Table, then Shade color? Windows 10 Help, How to do a Find in a Table, then Shade color? Office 2019
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 you have already marked the cells contents as bold and red or green then the following will shade the cells, although the colour you quoted is in fact a pale green?

Code:
Sub Macro1()
Dim oTable As Table
Dim oCell As Cell
    For Each oTable In ActiveDocument.Tables
        For Each oCell In oTable.Range.Cells
            If oCell.Range.Font.Bold = True Then
                If oCell.Range.Font.ColorIndex = wdRed Or _
                   oCell.Range.Font.ColorIndex = wdGreen Then
                    oCell.Shading.BackgroundPatternColor = -654246093
                End If
            End If
        Next oCell
    Next oTable
    Set oTable = Nothing
    Set oCell = Nothing
End Sub
__________________
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
  #3  
Old 01-08-2022, 05:04 AM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help, How to do a Find in a Table, then Shade color? Windows 10 Help, How to do a Find in a Table, then Shade color? Office 2019
Competent Performer
Help, How to do a Find in a Table, then Shade color?
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 190
Cendrinne is on a distinguished road
Default Thank you Graham, but it didn't work on my side

Hello, Graham,
I've tested it and it produce no color. The fact that you see the color code green versus orange, it depends on the Office Theme. By default, my Word document has the 2007-2010 office theme. I didn't mind to have the green or orange color, but neither worked.

In my initial request, I was hoping to add the valued searched for, to be Highlighted + if possible, to have the ''Text'' to do the search. Would that change to do the Find and Replace code?

I'm doing manually for now, but hopefully when I get a big documents, to not have to do it manually.

Thanks for trying though
Reply With Quote
  #4  
Old 01-08-2022, 06:49 AM
gmayor's Avatar
gmayor gmayor is offline Help, How to do a Find in a Table, then Shade color? Windows 10 Help, How to do a Find in a Table, then Shade color? Office 2019
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

Without access to a sample table, it is difficult to progress this further. The code looks for cell content that is Bold and coloured red or green. Highlight is not the issue so much as the search criteria.
Can you link an example document?
__________________
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
  #5  
Old 01-09-2022, 12:11 AM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help, How to do a Find in a Table, then Shade color? Windows 10 Help, How to do a Find in a Table, then Shade color? Office 2019
Competent Performer
Help, How to do a Find in a Table, then Shade color?
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 190
Cendrinne is on a distinguished road
Default I don't know how to create a link to have you see the doc....

Hi Graham,
As I've mentioned previously, I don't know how to create a link to have you access to a sample of a document yet. Besides, I would have to do it with my own computer.

Good news though I found a way to do it as a regular find and replace, by creating a Cell Style. And thank god, it didn't touch the Font Color nor the Highlighted Text, nor the Bold Font.

I can share my script, however, I don't now how to share my style.

But what I've done, is Select a Table Cell, and put it with the color of my choice, then instead of asking to Apply this to the Whole Table, I've requested to Apply it to the Top Left Cell, just as the image with the first table with a Red colored circle, from the link below:

Workaround for styles with table cells in Word - Office Watch

And here is my code script:

Code:
Sub FNR_TXT_Selec_Cell_Put_Colr_Orange()

    Selection.Find.ClearFormatting
    Selection.Find.Highlight = True
    With Selection.Find.Font
        .Bold = True
        .Color = 5287936                        'Green
    End With
    Selection.Find.Replacement.ClearFormatting
    Selection.Find.Replacement.Style = ActiveDocument.Styles( _
        "Cell Colr Org para")
    With Selection.Find
        .Text = "&"
        .Replacement.Text = "^&"
        .Forward = True
        .Wrap = wdFindStop
        .Format = True
        .MatchCase = True
        .MatchWholeWord = False
        .MatchWildcards = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
End Sub
I can't yet code like Pro's and I don't know why your code on my side, didn't do what it was intended to do, but I found a way to accomplish my need. The Green color code, well, it's numbers, that is because I've recorded my steps, and it gave me that number code. I guess I would have to create one for the red one. Unless I try to encorporate part of your script to do ''or''. I'll try later.

Just glad, this script works for my need. I do thank you for helping me though. It's really really great of you

Happy New Year my friend

Cendrinne
Attached Files
File Type: docx tst a macro3.docx (45.2 KB, 6 views)

Last edited by Cendrinne; 01-09-2022 at 05:14 PM. Reason: uploaded the word document
Reply With Quote
  #6  
Old 01-09-2022, 12:55 AM
gmayor's Avatar
gmayor gmayor is offline Help, How to do a Find in a Table, then Shade color? Windows 10 Help, How to do a Find in a Table, then Shade color? Office 2019
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 you click 'Go Advanced', then select the paper clip icon, you can attach a sample document to your post.
__________________
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
  #7  
Old 01-09-2022, 02:54 AM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help, How to do a Find in a Table, then Shade color? Windows 10 Help, How to do a Find in a Table, then Shade color? Office 2019
Competent Performer
Help, How to do a Find in a Table, then Shade color?
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 190
Cendrinne is on a distinguished road
Default I had to modify to do a Find & Replace only on Bold+Highlighted...

OK Thanks, I will try to do that with my own personal computer later.

Regarding my above Script, since myself I'm having trouble with many different color (could it be because it's an Index, vs wdColor name, or it's the Theme Office 2007 - 2010), my table didn't work if more than the Green color.

See below the Theme:
Change a theme and make it the default in Word or Excel

So I've opted to use only = IF some texte are Bold & Highlighted, Then Apply the Style with background Orange (color of my chosing, in the Style created prior on One cell).

I have not activated the colors below with the single apostrophe, but I wanted to show the coded numbers or color name, if you wanted to try. On my side, it didn't work. Oh and by the way, I had a new situation, where I wanted to ID it, so I had to add another color, DarkRed, as you will see in my new script.

Below, is the new script below:

Code:
    Selection.Find.ClearFormatting
    Selection.Find.Highlight = True
    With Selection.Find.Font
        .Bold = True
       '.Color = 5287936        'Green
       '.Color = 1857256        'Red
       '.Color = wdColorRed          'Red
       '.Color = wdColorDarkRed      'DarkRed
       '.Color = wdColorGreen        'Green
    End With
    Selection.Find.Replacement.ClearFormatting
    Selection.Find.Replacement.Style = ActiveDocument.Styles( _
        "Cell Colr Org para")
    With Selection.Find
        .Text = "&"
        .Replacement.Text = "^&"
        .Forward = True
        .Wrap = wdFindStop
        .Format = True
        .MatchCase = True
        .MatchWholeWord = False
        .MatchWildcards = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
As mentioned before, I will try from my personal PC a little later, to send you the test document, where the script above, didn't work. Been at this all night, so now I will sleep

Thanks for your patience my friend.

Cendrinne
Reply With Quote
  #8  
Old 01-09-2022, 05:19 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help, How to do a Find in a Table, then Shade color? Windows 10 Help, How to do a Find in a Table, then Shade color? Office 2019
Competent Performer
Help, How to do a Find in a Table, then Shade color?
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 190
Cendrinne is on a distinguished road
Default

Hello Graham,
Geeze took me a long time to figure out from DropBox to my desktop of personal computer, to enclosed the word table sample, where the script didn't work.

Now I hope the fact I've sent the word document to dropbox, then get it from my LabTop to the desktop to the forum, didn't't change modify the word document. Let me know if the your script works on this attached document. Please.

Thank you again, and have a great evening.

Learning curve

Cendrinne
Reply With Quote
  #9  
Old 01-09-2022, 10:37 PM
gmayor's Avatar
gmayor gmayor is offline Help, How to do a Find in a Table, then Shade color? Windows 10 Help, How to do a Find in a Table, then Shade color? Office 2019
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

Adding the document to your original message rather than your latest message caused a momentary confusion, but based on that document the following will work
Code:
Sub Macro1()
Dim oTable As Table
Dim oCell As Cell
Dim oRng As Range
    For Each oTable In ActiveDocument.Tables
        For Each oCell In oTable.Range.Cells
            Set oRng = oCell.Range
            With oRng.Find
                Do While .Execute(findText:="&")
                    If oRng.Font.Bold = True Then
                        If oRng.Font.ColorIndex = wdRed Or _
                           oRng.Font.ColorIndex = wdGreen Then
                            oRng.HighlightColorIndex = wdYellow
                            oCell.Shading.BackgroundPatternColor = &HD9E9FD
                        End If
                    End If
                    Exit Do
                Loop
            End With
        Next oCell
    Next oTable
    Set oTable = Nothing
     Set oCell = Nothing
    Set oRng = Nothing

 End Sub
I noted that one of the ampersands in the last row of the tables was not coloured red so that one is not processed. If you only want to process cells with bold ampersands regardless of colour, then remove the colour check e.g.
Code:
Sub Macro1()
Dim oTable As Table
Dim oCell As Cell
Dim oRng As Range
    For Each oTable In ActiveDocument.Tables
        For Each oCell In oTable.Range.Cells
            Set oRng = oCell.Range
            With oRng.Find
                Do While .Execute(findText:="&")
                    If oRng.Font.Bold = True Then
                        oRng.HighlightColorIndex = wdYellow
                        oCell.Shading.BackgroundPatternColor = &HD9E9FD
                    End If
                    Exit Do
                Loop
            End With
        Next oCell
    Next oTable
    Set oTable = Nothing
    Set oCell = Nothing
    Set oRng = Nothing

 End Sub
__________________
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 01-10-2022, 01:06 AM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help, How to do a Find in a Table, then Shade color? Windows 10 Help, How to do a Find in a Table, then Shade color? Office 2019
Competent Performer
Help, How to do a Find in a Table, then Shade color?
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 190
Cendrinne is on a distinguished road
Red face YES, it Worked on my side, wow, thanks.....

That is incredible. Thank god, you were able to do it, cause today, I had trouble even with my Styles. It didn't select the cell, but it selected the text, like paragraph background, not cell background. Yours works better than mine.

Thank you so much.

I've notice in your script, &HD9E9FD vs what I see on my side for the color codes: #FDE9D9? What are those? And why are they similar, yet different?
Could it be because my system is in French and yours in English, cause they look similar.

Hey it worked, that is all it count. I owe you all my gratitude


I learn alot with you guys. Getting better, after 6 years in Word, doing macros. One day, I'll reach closer to your level

***By the way, I appologize to have confused you with my document. I had a challenging time finding a way to do it. When one post worked, I went with it. It's my first time doing this, enclosing a doc, I mean.
Plus my personal PC, is a MacBood Pro, it was challending since I don't use it often.***

Thanks for your patience.

Cheers

Cendrinne
Reply With Quote
  #11  
Old 01-10-2022, 05:52 AM
gmayor's Avatar
gmayor gmayor is offline Help, How to do a Find in a Table, then Shade color? Windows 10 Help, How to do a Find in a Table, then Shade color? Office 2019
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

The colour code &HD9E9FD used in the macro is the sampled Visual Basic Hex Mode colour from your document using the invaluable ColorCop utility. This is the equivalent of RGB(253, 233, 217) and HTML Hex #FDE9D9
__________________
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
  #12  
Old 01-10-2022, 10:36 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help, How to do a Find in a Table, then Shade color? Windows 10 Help, How to do a Find in a Table, then Shade color? Office 2019
Competent Performer
Help, How to do a Find in a Table, then Shade color?
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 190
Cendrinne is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
The colour code &HD9E9FD used in the macro is the sampled Visual Basic Hex Mode colour from your document using the invaluable ColorCop utility. This is the equivalent of RGB(253, 233, 217) and HTML Hex #FDE9D9
Interesting. Thank you. I’ll look into that.

Many thanks again, Graham.

Cendrinne
Reply With Quote
Reply

Tags
macro help, shade a cell, word table macro

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help, How to do a Find in a Table, then Shade color? Conditionally Shade Table Cells S7ewie Mail Merge 8 04-29-2021 01:52 AM
Macro to change an RGB table cell shading color to another RGB color David Matthews Word VBA 4 05-29-2018 02:45 PM
Help, How to do a Find in a Table, then Shade color? Code to find a named (bookmarked?) table, replicate a row or table, and delete a specified table. kevinbradley57 Word VBA 9 09-21-2017 04:58 PM
Help, How to do a Find in a Table, then Shade color? Text area in table won't shade LSHAW Word Tables 1 03-09-2017 03:08 PM
Help, How to do a Find in a Table, then Shade color? VBA Table – Search All Tables - Find & Replace Text in Table Cell With Specific Background Color jc491 Word VBA 8 09-30-2015 06:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:46 AM.


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