Help to Highlight a cell that has less than 5 characters

Cendrinne · #1 11-07-2020, 01:55 AM

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

macropod · #2 11-07-2020, 02:53 AM

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

Cendrinne · #3 11-07-2020, 03:01 AM

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.

Cendrinne · #4 11-07-2020, 03:06 AM

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

Do you think it's doable? I had tried to put a coma in between the two words, but I got an error message.

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

Cendrinne · #5 11-07-2020, 03:09 AM

So is it possible?

macropod · #6 11-07-2020, 02:19 PM

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

but using Find/Replace would be more efficient:

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

Cendrinne · #7 11-08-2020, 07:28 PM

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

macropod · #8 11-08-2020, 07:31 PM

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.

Cendrinne · #9 11-08-2020, 07:37 PM

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

Since I have so many rows, scrolling with the mouse makes my mouse scroller stop functionning, so this helps

I'm not a pro YET, but I do get better.

Thanks a million Paul

Cendrinne · #10 11-08-2020, 07:54 PM

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

macropod · #11 11-08-2020, 09:10 PM

Quote:

Originally Posted by Cendrinne

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?

Presumably you're referring to an Excel macro. The logic - and much of the actual code - would be the same in Word.

Guessed · #12 11-08-2020, 10:47 PM

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.

macropod · #13 11-08-2020, 11:05 PM

I think the table's been substantially reduced in size now: https://www.msofficeforums.com/word-...hlighting.html

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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