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

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

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

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

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

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

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

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