Re: 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" <normanjones@xxxxxxxxxxxxxxxxxxx> wrote in message
news:OY2Bdp0lFHA.2904@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <chrisrowe_cr.1t4qan_1122973542.0101@xxxxxxxxxxxxxxxxxxxxx>
> wrote in message
> news:chrisrowe_cr.1t4qan_1122973542.0101@xxxxxxxxxxxxxxxxxxxxxxxx
>>
>> 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?action=getinfo&userid=25220
>> View this thread:
>> http://www.excelforum.com/showthread.php?threadid=392077
>>
>
>


.

Relevant Pages

  • Re: Adapting some code
    ... > Sub Find_Exceptions ... > Dim entry As Range, foundentry As Range, firstaddress As String ...
    (microsoft.public.excel.programming)
  • Re: adapting the findnext function
    ... Dim entry As Range, foundentry As Range, firstaddress As String ... > Sub FormatFoundValues() ... > Dim entry As Range, foundentry As Range, firstaddress As String ...
    (microsoft.public.excel.programming)
  • Adapting some code
    ... Dim entry As Range, foundentry As Range, firstaddress As String ... Dim rng1 As Range, Rng2 As Range ...
    (microsoft.public.excel.programming)
  • Re: Adapting some code
    ... Sub Find_Exceptions ... Dim entry As Range, foundentry As Range, firstaddress As String ... > Dim entry As Range, foundentry As Range, firstaddress As String ...
    (microsoft.public.excel.programming)
  • Re: Findind values in a worksheet and fornatting them
    ... Here is a modified version of the find next code Tom is referring to. ... Sub FormatFoundValues() ... Dim entry As Range, foundentry As Range, firstaddress As String ...
    (microsoft.public.excel.programming)