Goto location of another cell value

[CLR]

Hi All.....

If someone please, I am in need of code to select the cell in column B of
Sheet2, that contains a value matching that of cell B4 in Sheet1......and
then color the background of that cell RED. There will only be one cell in
Sheet2!B:B that will contain a matching value.

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Jim Thomlinson]

Sub FindStuff()
dim rngToFind as range
dim rngToSearch as range
dim rngFound as Range

set rngToFind = Sheets("Sheet1").Range("B4")
set rngToSeach = Sheets("Sheet2").Columns("B")
set rngFound = rngToSeach.Find(What:=rngTofind.Value, _
LookAt:=xlWhole, _
Lookin:=xlFormulas, _
MatchCase:=False)
if rngFound is nothing then
msgbox "Sorry... Not Found"
else
rngFound.Inerior.ColorIndex = 3
end if
end sub

 

[Jim Thomlinson]

Oops. Typo...
rngFound.Inerior.ColorIndex = 3
should be
rngFound.Interior.ColorIndex = 3

 

[CLR]

LOL.........no problem Jim, I found that one too........many "Thank you's",
it works perfectly for me...........BTW, if I could impose a little more
please.......could this be modified to also color the 3 cells to the right
of the rngFound?

Thanks again,
Vaya con Dios,
Chuck, CABGx3

 

[Jim Thomlinson]

Sub FindStuff()
dim rngToFind as range
dim rngToSearch as range
dim rngFound as Range

set rngToFind = Sheets("Sheet1").Range("B4")
set rngToSeach = Sheets("Sheet2").Columns("B")
set rngFound = rngToSeach.Find(What:=rngTofind.Value, _
LookAt:=xlWhole, _
Lookin:=xlFormulas, _
MatchCase:=False)
if rngFound is nothing then
msgbox "Sorry... Not Found"
else
rngFound.Resize(,3).Interior.ColorIndex = 3
end if
end sub

 

[CLR]

So cool that is..................."it's easy when you know how", <g>

Thanks Jim, you da man!

Vaya con Dios,
Chuck, CABGx3