#1
|
||||
|
||||
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 |
#2
|
||||
|
||||
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 |
#3
|
||||
|
||||
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 |
#4
|
||||
|
||||
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 |
#5
|
||||
|
||||
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 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 Last edited by Cendrinne; 01-09-2022 at 05:14 PM. Reason: uploaded the word document |
#6
|
||||
|
||||
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 |
#7
|
||||
|
||||
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 Thanks for your patience my friend. Cendrinne |
#8
|
||||
|
||||
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 |
#9
|
||||
|
||||
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 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 |
#10
|
||||
|
||||
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 |
#11
|
||||
|
||||
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 |
#12
|
||||
|
||||
Quote:
Many thanks again, Graham. Cendrinne |
Tags |
macro help, shade a cell, word table macro |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
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 |
Text area in table won't shade | LSHAW | Word Tables | 1 | 03-09-2017 03:08 PM |
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 |