Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adapting the findnext function
Hi all, I need to adapt the following code that was kindly given to me t include the red bit! I dont know any VB but i would guess that it only a minor mod? Sub FormatFoundValues() Dim entry As Range, foundentry As Range, firstaddress As String For Each entry In Range("b4:h76") **that is equal to an entry i the range j2:j30** Set foundentry = Cells.find(what:=entry.Value) If Not foundentry Is Nothing Then firstaddress = foundentry.Address Do With foundentry.Font .ColorIndex = 5 .Bold = True .Italic = True End With Set foundentry = Cells.FindNext(After:=foundentry) Loop While Not foundentry Is Nothing And foundentry.Addres < firstaddress End If Next entry End Su -- chrisrowe_c ----------------------------------------------------------------------- chrisrowe_cr's Profile: http://www.excelforum.com/member.php...fo&userid=2522 View this thread: http://www.excelforum.com/showthread.php?threadid=39207 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
adapting the findnext function
Hi Chris,
Try: '=============================== Sub FormatFoundValues() Dim entry As Range, foundentry As Range, firstaddress As String For Each entry In Range("b4:h76") If Not IsError(Application.Match _ (entry.Value, Range("J2:J30"), 0)) Then Set foundentry = Cells.Find(what:=entry.Value) If Not foundentry Is Nothing Then firstaddress = foundentry.Address Do With foundentry.Font .ColorIndex = 5 .Bold = True .Italic = True End With Set foundentry = Cells.FindNext(After:=foundentry) Loop While Not foundentry Is Nothing _ And foundentry.Address < firstaddress End If End If Next entry End Sub '<<================================== -- --- Regards, Norman "chrisrowe_cr" wrote in message news:chrisrowe_cr.1t4qan_1122973542.0101@excelforu m-nospam.com... Hi all, I need to adapt the following code that was kindly given to me to include the red bit! I dont know any VB but i would guess that its only a minor mod? Sub FormatFoundValues() Dim entry As Range, foundentry As Range, firstaddress As String For Each entry In Range("b4:h76") **that is equal to an entry in the range j2:j30** Set foundentry = Cells.find(what:=entry.Value) If Not foundentry Is Nothing Then firstaddress = foundentry.Address Do With foundentry.Font ColorIndex = 5 Bold = True Italic = True End With Set foundentry = Cells.FindNext(After:=foundentry) Loop While Not foundentry Is Nothing And foundentry.Address < firstaddress End If Next entry End Sub -- chrisrowe_cr ------------------------------------------------------------------------ chrisrowe_cr's Profile: http://www.excelforum.com/member.php...o&userid=25220 View this thread: http://www.excelforum.com/showthread...hreadid=392077 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
adapting the findnext function
Hi Chris,
And to limit formatting to the designated range, try instead: Sub FormatFoundValues() Dim entry As Range, foundentry As Range, firstaddress As String Dim rng1 As Range, Rng2 As Range Set rng1 = Range("b4:h76") Set Rng2 = Range("J2:J30") For Each entry In rng1 If Not IsError(Application.Match _ (entry.Value, Rng2, 0)) Then Set foundentry = rng1.Find(After:=rng1(1), _ What:=entry.Value, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext) If Not foundentry Is Nothing Then firstaddress = foundentry.Address Do With foundentry.Font .ColorIndex = 5 .Bold = True .Italic = True End With Set foundentry = rng1.FindNext(After:=foundentry) Loop While Not foundentry Is Nothing _ And foundentry.Address < firstaddress End If End If Next entry End Sub '<<====================================== -- --- Regards, Norman "Norman Jones" wrote in message ... Hi Chris, Try: '=============================== Sub FormatFoundValues() Dim entry As Range, foundentry As Range, firstaddress As String For Each entry In Range("b4:h76") If Not IsError(Application.Match _ (entry.Value, Range("J2:J30"), 0)) Then Set foundentry = Cells.Find(what:=entry.Value) If Not foundentry Is Nothing Then firstaddress = foundentry.Address Do With foundentry.Font .ColorIndex = 5 .Bold = True .Italic = True End With Set foundentry = Cells.FindNext(After:=foundentry) Loop While Not foundentry Is Nothing _ And foundentry.Address < firstaddress End If End If Next entry End Sub '<<================================== -- --- Regards, Norman "chrisrowe_cr" wrote in message news:chrisrowe_cr.1t4qan_1122973542.0101@excelforu m-nospam.com... Hi all, I need to adapt the following code that was kindly given to me to include the red bit! I dont know any VB but i would guess that its only a minor mod? Sub FormatFoundValues() Dim entry As Range, foundentry As Range, firstaddress As String For Each entry In Range("b4:h76") **that is equal to an entry in the range j2:j30** Set foundentry = Cells.find(what:=entry.Value) If Not foundentry Is Nothing Then firstaddress = foundentry.Address Do With foundentry.Font ColorIndex = 5 Bold = True Italic = True End With Set foundentry = Cells.FindNext(After:=foundentry) Loop While Not foundentry Is Nothing And foundentry.Address < firstaddress End If Next entry End Sub -- chrisrowe_cr ------------------------------------------------------------------------ chrisrowe_cr's Profile: http://www.excelforum.com/member.php...o&userid=25220 View this thread: http://www.excelforum.com/showthread...hreadid=392077 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
adapting the findnext function
surely some one can help? -- chrisrowe_c ----------------------------------------------------------------------- chrisrowe_cr's Profile: http://www.excelforum.com/member.php...fo&userid=2522 View this thread: http://www.excelforum.com/showthread.php?threadid=39207 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
adapting the findnext function
hmm, I tried this and woohoo it does work only it doesnt work... I figured out why tho, the values I want to format are all being pulle in via a lookup, is there anyway to make excel think they are just ther and not part of a formula -- chrisrowe_c ----------------------------------------------------------------------- chrisrowe_cr's Profile: http://www.excelforum.com/member.php...fo&userid=2522 View this thread: http://www.excelforum.com/showthread.php?threadid=39207 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
adapting the findnext function
Hi Chris,
Try: '====================================== Sub FormatFoundValues2() Dim entry As Range, foundentry As Range, firstaddress As String Dim rng1 As Range, Rng2 As Range Set rng1 = Range("b4:h76") Set Rng2 = Range("J2:J30") For Each entry In rng1 If Not IsError(Application.Match _ (entry.Value, Rng2, 0)) Then Set foundentry = rng1.Find(After:=rng1(1), _ What:=entry.Value, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ LookIn:=xlFormulas) ' <<====== ADDED If Not foundentry Is Nothing Then firstaddress = foundentry.Address Do With foundentry.Font .ColorIndex = 5 .Bold = True .Italic = True End With Set foundentry = rng1.FindNext(After:=foundentry) Loop While Not foundentry Is Nothing _ And foundentry.Address < firstaddress End If End If Next entry End Sub '<<====================================== -- --- Regards, Norman "chrisrowe_cr" wrote in message news:chrisrowe_cr.1t56yo_1122995160.4079@excelforu m-nospam.com... hmm, I tried this and woohoo it does work only it doesnt work... I figured out why tho, the values I want to format are all being pulled in via a lookup, is there anyway to make excel think they are just there and not part of a formula? -- chrisrowe_cr ------------------------------------------------------------------------ chrisrowe_cr's Profile: http://www.excelforum.com/member.php...o&userid=25220 View this thread: http://www.excelforum.com/showthread...hreadid=392077 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
adapting the findnext function
LookIn:=xlValues, LookAt:=xlWhole
Lookin:=xlValues says to look at what the formula produces and not at the formula itself. Lookat:=xlWhole means the match must be to the whole value of the cell. if you used xlpart, it would match "and" to "Sand" for example -- Regards, Tom Ogilvy "chrisrowe_cr" wrote in message news:chrisrowe_cr.1t56yo_1122995160.4079@excelforu m-nospam.com... hmm, I tried this and woohoo it does work only it doesnt work... I figured out why tho, the values I want to format are all being pulled in via a lookup, is there anyway to make excel think they are just there and not part of a formula? -- chrisrowe_cr ------------------------------------------------------------------------ chrisrowe_cr's Profile: http://www.excelforum.com/member.php...o&userid=25220 View this thread: http://www.excelforum.com/showthread...hreadid=392077 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
adapting the findnext function
Hi Tom,
Thank you! My brain and my typing fingers were at variance. --- Regards, Norman "Tom Ogilvy" wrote in message ... LookIn:=xlValues, LookAt:=xlWhole Lookin:=xlValues says to look at what the formula produces and not at the formula itself. Lookat:=xlWhole means the match must be to the whole value of the cell. if you used xlpart, it would match "and" to "Sand" for example -- Regards, Tom Ogilvy "chrisrowe_cr" wrote in message news:chrisrowe_cr.1t56yo_1122995160.4079@excelforu m-nospam.com... hmm, I tried this and woohoo it does work only it doesnt work... I figured out why tho, the values I want to format are all being pulled in via a lookup, is there anyway to make excel think they are just there and not part of a formula? -- chrisrowe_cr ------------------------------------------------------------------------ chrisrowe_cr's Profile: http://www.excelforum.com/member.php...o&userid=25220 View this thread: http://www.excelforum.com/showthread...hreadid=392077 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adapting MAX function | Excel Worksheet Functions | |||
Multi line merged cell - adapting to different size input - possible? | Excel Discussion (Misc queries) | |||
Findnext | Excel Discussion (Misc queries) | |||
Help needed with Adapting complex INDEX formula | Excel Worksheet Functions | |||
FindNext | Excel Programming |