Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-15-2012, 01:52 AM
gsrikanth gsrikanth is offline find the word and highlighst vba extention Windows XP find the word and highlighst vba extention Office XP
Competent Performer
find the word and highlighst vba extention
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default find the word and highlighst vba extention

below code is for one word monkey
in this only i want to search more words how?
Sub example()

Dim iLoop As Integer
Dim i As Integer
Dim rNa As Range

' count number of monkeys in Activesheet (change to sheet required)
iLoop = WorksheetFunction.CountIf(ActiveSheet.UsedRange, "*" & "monkey" & "*")

' loop through and find monkeys
Set rNa = ActiveSheet.Range("A1") ' change sheet if necessary
For i = 1 To iLoop
Set rNa = Cells.Find(What:="monkey", After:=rNa, _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
' highlight the rows


rNa.EntireRow.Interior.ColorIndex = 36
Next i

End Sub
Reply With Quote
  #2  
Old 03-15-2012, 02:23 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline find the word and highlighst vba extention Windows 7 32bit find the word and highlighst vba extention Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Why don't you use conditional formatting instead of VBA?
Reply With Quote
  #3  
Old 03-15-2012, 02:24 AM
gsrikanth gsrikanth is offline find the word and highlighst vba extention Windows XP find the word and highlighst vba extention Office XP
Competent Performer
find the word and highlighst vba extention
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default

Quote:
Originally Posted by Colin Legg View Post
Why don't you use conditional formatting instead of VBA?
i have 47 word come once, once 100 word like so on
generallies code to put all words like "monkey" & "SP10.JPG" & "TIGER" & "JE10.IMG"
Reply With Quote
  #4  
Old 03-15-2012, 02:44 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline find the word and highlighst vba extention Windows 7 32bit find the word and highlighst vba extention Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

I have attached a workbook with two conditional formatting examples.

Example 1

will hightlight E3:M31 if any of the cells have an exact match to any word listed in A3:A20. The conditional formatting formula is:

=OR(E3=$A$3:$A$20)


Example 2

will hightlight E38:M66 if any of the cells contain any word listed in A38:A55. The conditional formatting formula is:

=OR(ISNUMBER(FIND($A$38:$A$55,E38)))

This is case sensitive. If you do not want it do be case sensitive then use SEARCH() instead of FIND().
Attached Files
File Type: xls Book1.xls (36.0 KB, 11 views)
Reply With Quote
  #5  
Old 03-15-2012, 03:13 AM
gsrikanth gsrikanth is offline find the word and highlighst vba extention Windows XP find the word and highlighst vba extention Office XP
Competent Performer
find the word and highlighst vba extention
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default

Quote:
Originally Posted by Colin Legg View Post
I have attached a workbook with two conditional formatting examples.

Example 1

will hightlight E3:M31 if any of the cells have an exact match to any word listed in A3:A20. The conditional formatting formula is:

=OR(E3=$A$3:$A$20)


Example 2

will hightlight E38:M66 if any of the cells contain any word listed in A38:A55. The conditional formatting formula is:

=OR(ISNUMBER(FIND($A$38:$A$55,E38)))

This is case sensitive. If you do not want it do be case sensitive then use SEARCH() instead of FIND().
example 2 is suitable for me but where to put formula i have hundred words
Reply With Quote
  #6  
Old 03-15-2012, 03:19 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline find the word and highlighst vba extention Windows 7 32bit find the word and highlighst vba extention Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

List 100 words on the spreadsheet like I did. The formula goes in the conditional formatting section, just the same as my example except you need to change the ranges it uses. Conditional formatting tutorial is here:
http://www.contextures.com/xlcondformat03.html
Reply With Quote
  #7  
Old 03-15-2012, 07:37 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline find the word and highlighst vba extention Windows XP find the word and highlighst vba extention Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Cross post http://www.excelforum.com/excel-prog...eneralies.html

http://www.mrexcel.com/forum/showthr...17#post3081317
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
find the word and highlighst vba extention Word VBA - Find SDondeti Word VBA 4 08-15-2012 05:03 PM
find the word and highlighst vba extention Bad view when using Find and Find & Replace - Word places found string on top line paulkaye Word 4 12-06-2011 11:05 PM
find&replace word in uppercase with word in lowercase andrei Word 3 10-03-2011 05:11 AM
find the word and highlighst vba extention where we can find the meaning for all the marks that Word uses? Jamal NUMAN Word 2 06-26-2011 09:49 AM
How to find out the version of the word files? mariaprabudass Word 0 06-18-2010 05:40 AM

Other Forums: Access Forums

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