#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
Why don't you use conditional formatting instead of VBA?
|
#3
|
|||
|
|||
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" |
#4
|
||||
|
||||
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(). |
#5
|
|||
|
|||
Quote:
|
#6
|
||||
|
||||
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 |
#7
|
||||
|
||||
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 |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Word VBA - Find | SDondeti | Word VBA | 4 | 08-15-2012 05:03 PM |
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 |
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 |