Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-07-2020, 01:55 AM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help to Highlight a cell that has less than 5 characters Windows 10 Help to Highlight a cell that has less than 5 characters Office 2019
Competent Performer
Help to Highlight a cell that has less than 5 characters
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 190
Cendrinne is on a distinguished road
Default 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...
Reply With Quote
  #2  
Old 11-07-2020, 02:53 AM
macropod's Avatar
macropod macropod is offline Help to Highlight a cell that has less than 5 characters Windows 10 Help to Highlight a cell that has less than 5 characters Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 11-07-2020, 03:01 AM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help to Highlight a cell that has less than 5 characters Windows 10 Help to Highlight a cell that has less than 5 characters Office 2019
Competent Performer
Help to Highlight a cell that has less than 5 characters
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 190
Cendrinne is on a distinguished road
Smile 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.
Reply With Quote
  #4  
Old 11-07-2020, 03:06 AM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help to Highlight a cell that has less than 5 characters Windows 10 Help to Highlight a cell that has less than 5 characters Office 2019
Competent Performer
Help to Highlight a cell that has less than 5 characters
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 190
Cendrinne is on a distinguished road
Default

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

Last edited by Cendrinne; 11-07-2020 at 09:11 AM. Reason: removed a line not necessary
Reply With Quote
  #5  
Old 11-07-2020, 03:09 AM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help to Highlight a cell that has less than 5 characters Windows 10 Help to Highlight a cell that has less than 5 characters Office 2019
Competent Performer
Help to Highlight a cell that has less than 5 characters
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 190
Cendrinne is on a distinguished road
Default

So is it possible?

Last edited by Cendrinne; 11-07-2020 at 09:14 AM.
Reply With Quote
  #6  
Old 11-07-2020, 02:19 PM
macropod's Avatar
macropod macropod is offline Help to Highlight a cell that has less than 5 characters Windows 10 Help to Highlight a cell that has less than 5 characters Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 11-08-2020, 07:28 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help to Highlight a cell that has less than 5 characters Windows 10 Help to Highlight a cell that has less than 5 characters Office 2019
Competent Performer
Help to Highlight a cell that has less than 5 characters
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 190
Cendrinne is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 11-08-2020, 07:31 PM
macropod's Avatar
macropod macropod is offline Help to Highlight a cell that has less than 5 characters Windows 10 Help to Highlight a cell that has less than 5 characters Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #9  
Old 11-08-2020, 07:37 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help to Highlight a cell that has less than 5 characters Windows 10 Help to Highlight a cell that has less than 5 characters Office 2019
Competent Performer
Help to Highlight a cell that has less than 5 characters
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 190
Cendrinne is on a distinguished road
Default

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
Reply With Quote
  #10  
Old 11-08-2020, 07:54 PM
Cendrinne's Avatar
Cendrinne Cendrinne is offline Help to Highlight a cell that has less than 5 characters Windows 10 Help to Highlight a cell that has less than 5 characters Office 2019
Competent Performer
Help to Highlight a cell that has less than 5 characters
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 190
Cendrinne is on a distinguished road
Default

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
Reply With Quote
  #11  
Old 11-08-2020, 09:10 PM
macropod's Avatar
macropod macropod is offline Help to Highlight a cell that has less than 5 characters Windows 10 Help to Highlight a cell that has less than 5 characters Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Cendrinne View Post
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 11-08-2020, 10:47 PM
Guessed's Avatar
Guessed Guessed is offline Help to Highlight a cell that has less than 5 characters Windows 10 Help to Highlight a cell that has less than 5 characters Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #13  
Old 11-08-2020, 11:05 PM
macropod's Avatar
macropod macropod is offline Help to Highlight a cell that has less than 5 characters Windows 10 Help to Highlight a cell that has less than 5 characters Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Tags
highlight cells, less than 5 characters

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help to Highlight a cell that has less than 5 characters Highlight only the characters ER when it occurs in SEVERE. norgro Word VBA 3 02-18-2013 05:14 PM
Help to Highlight a cell that has less than 5 characters How to highlight the row based on cell value? Learner7 Excel 1 12-11-2011 02:28 AM
Help to Highlight a cell that has less than 5 characters 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:00 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