The problem is this line

Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400")

which although it looks as though it picks two separate areas it actually
picks one area of B1:AI400, which includes R6:R10 (and R:R5 and R7:R400).
What you want is

With Worksheets("sheet1")
Set oColors = Union(.Range("B1:Q400"), Range("T1:AI400"))
End With

instead of that line, which creates a union of the two areas, and then works
on that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sharon" wrote in message
...
Your explanation was wonderful! Thank you so much! Here's what I did
(and
it works, except for one little quirk) :)

Sub FindAndReplace()
Dim oColors As Range
Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400")
oColors.Replace What:="green", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
oColors.Replace What:="yellow", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
oColors.Replace What:="red", Replacement:="3", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
oColors.Replace What:="no", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

The little quirk is that I have a Key in column R6:R10 that says:
Green = 1
Yellow = 2
Red = 3
No = 4

When I run the macro, it changes the words, "Green", "Yellow", "Red", and
"No" to numbers as well, even though that range of cells is not defined in
my
range.

Any idea how to make it stop doing that?

Grateful for help,

Sharon

"Bob Phillips" wrote:

Sharon,

The problem seems to lie with the line

Set Colors = Colors.Range("B1:Q400", "S1:AI400")

What this is saying is set the Colors variable to refer to the Colors
sheet,
ranges ("B1:Q400" and "S1:AI400". Note that it is the worksheets
codename
that you are using, not its Excel name. If you really have a worksheet
codename of Colors, you have a conflict as you have 2 objects with the
same
name. You can either use

Set Colors = Range("B1:Q400", "S1:AI400")

if you just want to pick up the activesheet, or

Set Colors = Worksheets("Colors").Range("B1:Q400", "S1:AI400")

if you want to refer explicitly to a worksheet (a good practice) with
the
Excel name of Colors, or

Set Colors = wsColors.Range("B1:Q400", "S1:AI400")

if you set the worksheet codename to wsColors.

BTW, this sort of conflict is partly why many of us use naming
conventions
for our variables, and objects. I use sName for string variables, cName
for
counting variables, iNAme for indexing variables, and oName for objects.
For
objects , I sometimes further qualify like oRngName, or oWsName, for
greater
clarity.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sharon" wrote in message
...
I hope this post makes sense. I am trying to learn VB on my own and I
know
that I need to declare my objects and know that I would prefer to
declare
them early in the code.

I want to write a macro that does a find and replace for "Green",
"Yellow",
"Red", and "No" on Sheet1 and replaces the words with the values 1, 2,
3,
4,
respectively. The range of my data is from B1:Q400 and S1:AI400. I
named
the non-contiguous range "Colors". I would like to use the name of my
range
in my code.

Below is my first attempt to writing a macro, partially using the
macro
recorder. The code the recorder gave me was:

Sub FindAndReplace()

Selection.Replace What:="green", Replacement:="1", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="yellow", Replacement:="2",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="red", Replacement:="3", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="no", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

End Sub

Since I don't want the user to have to select the range each time to
run
the
macro, I want to change the code to apply to the range I named
"Colors".
So
here's my first attempt at declaring my "Colors" range as my object.
Except,
since I don't really know what I'm doing, it doesn't work.

Can anyone help me declare my range "Colors"?

Sub FindAndReplace()

Dim Colors As Range
Set Colors = Colors.Range("B1:Q400", "S1:AI400")

Colors.Replace What:="green", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Colors.Replace What:="yellow", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Colors.Replace What:="red", Replacement:="3", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Colors.Replace What:="no", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Thanks so much,

Sharon