#1
|
||||
|
||||
Help to Highlight a cell that has less than 5 characters
Hello pro's,
Been searching Google and Bing, to find a script if a Word cell table has less then 5 characteres, highlight the cell. Note, the table is normally of 2 columns but will have maybe over 5-10,000 rows. (A very long table). It would be easier for me to find such cell, if It's highlighted. I've tried, but can't find a way yet. Any insights? / Guidance? Thanks in advance, Cendrinne Last edited by Cendrinne; 11-07-2020 at 02:25 AM. Reason: Corr the subject, had a typo... |
#2
|
||||
|
||||
For example:
Code:
Sub Demo() Application.ScreenUpdating = False Dim oCell For Each oCell In Selection.Tables(1).Range.Cells With oCell.Range If Len(.Text) > 2 Then If Len(.Text) < 7 Then .HighlightColorIndex = wdYellow End If End With Next Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
||||
|
||||
Thank you so much, Paul, that works
OMG, why when you do it, it seems so logical, but when I research and try bits of pieces and try to make a script, I don't have it. Keeps giving me errors.
I hope one day to be as logical and have a found understanding to coding like you do. Thanks a million. |
#4
|
||||
|
||||
I had concutted a script that worked for one specific word. But I would have loved to find both or more specific words with this script.
Code:
Sub Find_5_Letters_and_less_in_cell() 'Chr(13) = CR Carriage Return // Chr(7) = BEL or EndMarker Dim tTbl As Table Dim oCell As cell Dim oRng As range Application.ScreenUpdating = False Set tTbl = Selection.Tables(1) 'For Each tTbl In ActiveDocument.range.Tables For Each oCell In tTbl.range.Cells 'If oCell.range.Text = "Inc." + Chr(13) + Chr(7) Then If oCell.range.Text = "Ltd." + Chr(13) + Chr(7) Then oCell.range.HighlightColorIndex = wdYellow End If Next oCell 'Next Application.ScreenUpdating = True Application.ScreenRefresh DoEvents lbl_Exit: Set tTbl = Nothing Set oRng = Nothing Exit Sub On Error GoTo 0 End Sub The reason I ask, is because when I have a list of companies name with Ltd. and Inc., 10% of the time, I find it on a separate row alone in a cell. So when I have a long table, over 5K rows, scrolling is challengind. So I'm finding a difficult time, looking for these specific short words. And there could be more. Cendrinne Last edited by Cendrinne; 11-07-2020 at 09:11 AM. Reason: removed a line not necessary |
#5
|
||||
|
||||
So is it possible?
Last edited by Cendrinne; 11-07-2020 at 09:14 AM. |
#6
|
||||
|
||||
Yes, it's possible to test for specific strings. For example:
Code:
Sub Demo() Application.ScreenUpdating = False Dim oCell For Each oCell In Selection.Tables(1).Range.Cells With oCell.Range If Split(.Text, vbCr)(0) = "Ltd." Then .HighlightColorIndex = wdYellow If Split(.Text, vbCr)(0) = "Inc." Then .HighlightColorIndex = wdYellow End With Next Application.ScreenUpdating = True End Sub Code:
Sub Demo() Application.ScreenUpdating = False Dim Rng As Range With Selection.Tables(1) Set Rng = .Range With .Range With .Find .ClearFormatting .Replacement.ClearFormatting .Text = "[LI][tn][dc]." .Replacement.Text = "" .Forward = True .Wrap = wdFindStop .Format = False .MatchWildcards = True End With Do While .Find.Execute If .InRange(Rng) = False Then Exit Do With .Cells(1).Range If Split(.Text, vbCr)(0) = "Ltd." Then .HighlightColorIndex = wdYellow If Split(.Text, vbCr)(0) = "Inc." Then .HighlightColorIndex = wdYellow End With .Collapse wdCollapseEnd Loop End With End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
||||
|
||||
YES!!!!!!! You've done it. It works Thank you so much. My god you pro's are so incredible. Believe me, it's been 5 years I've been trying to figure out stuff. I do get better, but I'm sooo far behind your skills
Now question, you recommend I use the second one over the first. Is there a reason? Just trying to understand stuff Cendrinne |
#8
|
||||
|
||||
Speed. A Find/Replace doesn't need to test each cell. If there are many cells, viz "maybe over 5-10,000 rows. (A very long table)", Find/Replace is likely to be much faster.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
||||
|
||||
ahhhhhh yes I see, and I do get many cells. This type of work, I do have 5-10,000 Rows.
That is a very logical answer. Thank you I even created a macro to find the next cell that starts with highlight. and it works Code:
Sub Find_Next_Start_Cell_w_Highlight() ' ' Selection.Find.ClearFormatting Selection.Find.Highlight = True With Selection.Find .Text = "<?" .Replacement.Text = "" .Forward = True .Wrap = wdFindAsk .Format = True .MatchCase = False .MatchWildcards = True End With Selection.Find.Execute End Sub I'm not a pro YET, but I do get better. Thanks a million Paul |
#10
|
||||
|
||||
One last question for tonight. I'm not sure if it's doable. I know in Excel we can find duplicates in rows / cells. Is there such a command in Word?
But I would like to flag within it's column. Don't need to have the script now, I was just checking if it's doable first of all in Word, cause Excel and Word does have similar scripts but some can't cross over. And one day, I'll attempt to do it first I only ask so I won't waste any time if Word doesn't have that command. Cendrinne |
#11
|
||||
|
||||
Presumably you're referring to an Excel macro. The logic - and much of the actual code - would be the same in Word.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
||||
|
||||
Excel's Remove Duplicates function is a built-in command that is not available in Word. If you are dealing with tables containing 5-10k rows then I would hazard a guess and say that Excel would probably be a better application to be working in. This would depend on the actual content you have in your tables though.
It would be possible to create a macro in Word that mimics the Excel functionality but it would take a lot of work and require a userform to give you the dynamic controls that Excel's function does.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#13
|
||||
|
||||
I think the table's been substantially reduced in size now: https://www.msofficeforums.com/word-...hlighting.html
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
highlight cells, less than 5 characters |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Highlight only the characters ER when it occurs in SEVERE. | norgro | Word VBA | 3 | 02-18-2013 05:14 PM |
How to highlight the row based on cell value? | Learner7 | Excel | 1 | 12-11-2011 02:28 AM |
Highlight the first X number of characters | 14spar15 | Word | 1 | 11-13-2011 11:17 PM |
find - reading highlight - highlight all / highlight doesn't stick when saved | bobk544 | Word | 3 | 04-15-2009 03:31 PM |
highlight cell after checkbox | flatk | Word | 0 | 01-25-2007 12:32 PM |