Adapting some code
 

Hi all,

Some of you were kind enough to help me with this bit of code a littl
while ago, I now need to adapt it however. I need to add a 3rd rang
ie (o2:o20) and alter anything in that range to say, grey text


Sub Find_Exceptions()

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("M2:M20")

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:=xlValues, LookAt:=xlWhole)
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

is this possible?
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=39604

Adapting some code
 

Chris,

Is this what you need?

Sub Find_Exceptions()

Dim entry As Range, foundentry As Range, firstaddress As String
Dim rng1 As Range, Rng2 As Range, Rng3 As Range

Set rng1 = Range("B4:H76")
Set Rng2 = Range("M2:M20")
Set Rng3 = Range("O2:O20")

For Each entry In rng1
If Not IsError(Application.Match _
(entry.Value, Rng2, 0)) Or _
Not IsError(Application.Match _
(entry.Value, Rng3, 0)) Then
Set foundentry = rng1.Find(After:=rng1(1), _
What:=entry.Value, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
LookIn:=xlValues, LookAt:=xlWhole)
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




--
HTH

Bob Phillips

"chrisrowe_cr"
wrote in message
news:chrisrowe_cr.1tunme_1124183121.8437@excelforu m-nospam.com...

Hi all,

Some of you were kind enough to help me with this bit of code a little
while ago, I now need to adapt it however. I need to add a 3rd range
ie (o2:o20) and alter anything in that range to say, grey text


Sub Find_Exceptions()

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("M2:M20")

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:=xlValues, LookAt:=xlWhole)
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

is this possible?
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=396048

Adapting some code
 

Bob Phillips Wrote:
Chris,

Is this what you need?

Sub Find_Exceptions()

Dim entry As Range, foundentry As Range, firstaddress As String
Dim rng1 As Range, Rng2 As Range, Rng3 As Range

Set rng1 = Range("B4:H76")
Set Rng2 = Range("M2:M20")
Set Rng3 = Range("O2:O20")

For Each entry In rng1
If Not IsError(Application.Match _
(entry.Value, Rng2, 0)) Or _
Not IsError(Application.Match _
(entry.Value, Rng3, 0)) Then
Set foundentry = rng1.Find(After:=rng1(1), _
What:=entry.Value, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
LookIn:=xlValues, LookAt:=xlWhole)
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




--
HTH

Bob Phillips

"chrisrowe_cr"

wrote in message
news:chrisrowe_cr.1tunme_1124183121.8437@excelforu m-nospam.com...

Hi all,

Some of you were kind enough to help me with this bit of code a
little
while ago, I now need to adapt it however. I need to add a 3rd
range
ie (o2:o20) and alter anything in that range to say, grey text


Sub Find_Exceptions()

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("M2:M20")

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:=xlValues, LookAt:=xlWhole)
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

is this possible?
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=396048



Uhm, I need to set the values in Range 2 as one thing and the values in
Range 3 as another. ie Range 2 values Blue Bold Italic, Range 3 values
Grey Italic...


--
chrisrowe_cr
------------------------------------------------------------------------
chrisrowe_cr's Profile: http://www.excelforum.com/member.php...o&userid=25220
View this thread: http://www.excelforum.com/showthread...hreadid=396048

Adapting some code
 

I am confused, your original code set the rng1 values!

--
HTH

Bob Phillips

"chrisrowe_cr"
wrote in message
news:chrisrowe_cr.1tut6p_1124190330.9775@excelforu m-nospam.com...

Bob Phillips Wrote:
Chris,

Is this what you need?

Sub Find_Exceptions()

Dim entry As Range, foundentry As Range, firstaddress As String
Dim rng1 As Range, Rng2 As Range, Rng3 As Range

Set rng1 = Range("B4:H76")
Set Rng2 = Range("M2:M20")
Set Rng3 = Range("O2:O20")

For Each entry In rng1
If Not IsError(Application.Match _
(entry.Value, Rng2, 0)) Or _
Not IsError(Application.Match _
(entry.Value, Rng3, 0)) Then
Set foundentry = rng1.Find(After:=rng1(1), _
What:=entry.Value, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
LookIn:=xlValues, LookAt:=xlWhole)
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




--
HTH

Bob Phillips

"chrisrowe_cr"

wrote in message
news:chrisrowe_cr.1tunme_1124183121.8437@excelforu m-nospam.com...

Hi all,

Some of you were kind enough to help me with this bit of code a
little
while ago, I now need to adapt it however. I need to add a 3rd
range
ie (o2:o20) and alter anything in that range to say, grey text


Sub Find_Exceptions()

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("M2:M20")

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:=xlValues, LookAt:=xlWhole)
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

is this possible?
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=396048



Uhm, I need to set the values in Range 2 as one thing and the values in
Range 3 as another. ie Range 2 values Blue Bold Italic, Range 3 values
Grey Italic...


--
chrisrowe_cr
------------------------------------------------------------------------
chrisrowe_cr's Profile:
http://www.excelforum.com/member.php...o&userid=25220
View this thread: http://www.excelforum.com/showthread...hreadid=396048

Adapting some code
 

anyone able to help?


--
chrisrowe_cr
------------------------------------------------------------------------
chrisrowe_cr's Profile: http://www.excelforum.com/member.php...o&userid=25220
View this thread: http://www.excelforum.com/showthread...hreadid=396048