a little complicated programming, help would be appreciated!
 

good morning all.

this is a little complicated, so ill start from the beginning....

and before you ask, i dont have access and cant get it, hence why i am using
excel,

i have a workbook, there are two work sheets which contain various data, but
ideally should be kept separate as they are for separate years.

i have written a macro which will search the sheets for a specific name or
reference number, then will highlight the row where that cell lies.

here is the problem...
if the file opens on sheet one and i search for a specific name which i know
is on sheet two, then the corresponding row is highlighted in sheet one.
how do i write into the code so that it highlights and then makes it active
so you can see it on sheet two were the actal name is?

if you need to see the code that i have at the moment let me know and i will
add it in.
thanks

mike

a little complicated programming, help would be appreciated!
 

Hi Mike,

How is the macro to know which sheet should be searched?

---
Regards,
Norman



"mike" wrote in message
...
good morning all.

this is a little complicated, so ill start from the beginning....

and before you ask, i dont have access and cant get it, hence why i am
using
excel,

i have a workbook, there are two work sheets which contain various data,
but
ideally should be kept separate as they are for separate years.

i have written a macro which will search the sheets for a specific name or
reference number, then will highlight the row where that cell lies.

here is the problem...
if the file opens on sheet one and i search for a specific name which i
know
is on sheet two, then the corresponding row is highlighted in sheet one.
how do i write into the code so that it highlights and then makes it
active
so you can see it on sheet two were the actal name is?

if you need to see the code that i have at the moment let me know and i
will
add it in.
thanks

mike

a little complicated programming, help would be appreciated!
 

Good Question.... here is the code as it stands....

Sub auto_open()
Dim start, x, y, FR
start = "Do you wish to use the Search Tool?"
x = vbOKCancel + vbQuestion + vbDefaultButton1
y = "TITLE"
FR = MsgBox(start, x, y)
If FR = vbOK Then
Dim Msg, style, Title, Help, Ctxt, Response, MyString
Msg = "MESSAGE."
style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "TITLE"
Response = MsgBox(Msg, style, Title)
If Response = vbYes Then
Dim answer As String
answer = InputBox(INPUTBOX)
If answer = Empty Then
MsgBox Prompt:= MESSAGE
Else
With Worksheets("SHEET ONE").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET ONE").Range("M2:IV500")
d.Interior.ColorIndex = Null
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET TWO").Range("M2:IV500")
d.Interior.ColorIndex = Null
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End If
Else
Dim Cname As String
Cname = InputBox(INPUTBOX)
If Cname = Empty Then
MsgBox Prompt:=MESSAGE
Else
With Worksheets("SHEET ONE").Range("d4:d100")
Set z = .Find(Cname, LookIn:=xlValues)
If Not z Is Nothing Then
firstAddress = z.Address
Do
Rows(z.Row & ":" & z.Row).Interior.ColorIndex = 8
Set y = Worksheets("SHEET ONE").Range("M2:IV500")
y.Interior.ColorIndex = Null
Set z = .FindNext(z)
Loop While Not z Is Nothing And z.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("d2:d100")
Set w = .Find(Cname, LookIn:=xlValues)
If Not w Is Nothing Then
firstAddress = w.Address
Do
Rows(w.Row & ":" & w.Row).Interior.ColorIndex = 8
Set v = Worksheets("SHEET TWO").Range("M2:IV500")
v.Interior.ColorIndex = Null
Set w = .FindNext(w)
Loop While Not w Is Nothing And w.Address < firstAddress
End If
End With
End If
End If
End If
End Sub

Thanks Norman, hope you can help.

Mike


"Norman Jones" wrote:

Hi Mike,

How is the macro to know which sheet should be searched?

---
Regards,
Norman



"mike" wrote in message
...
good morning all.

this is a little complicated, so ill start from the beginning....

and before you ask, i dont have access and cant get it, hence why i am
using
excel,

i have a workbook, there are two work sheets which contain various data,
but
ideally should be kept separate as they are for separate years.

i have written a macro which will search the sheets for a specific name or
reference number, then will highlight the row where that cell lies.

here is the problem...
if the file opens on sheet one and i search for a specific name which i
know
is on sheet two, then the corresponding row is highlighted in sheet one.
how do i write into the code so that it highlights and then makes it
active
so you can see it on sheet two were the actal name is?

if you need to see the code that i have at the moment let me know and i
will
add it in.
thanks

mike

a little complicated programming, help would be appreciated!
 

You can get MySQL, which is a free database.

--

HTH

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


"mike" wrote in message
...
good morning all.

this is a little complicated, so ill start from the beginning....

and before you ask, i dont have access and cant get it, hence why i am
using
excel,

i have a workbook, there are two work sheets which contain various data,
but
ideally should be kept separate as they are for separate years.

i have written a macro which will search the sheets for a specific name or
reference number, then will highlight the row where that cell lies.

here is the problem...
if the file opens on sheet one and i search for a specific name which i
know
is on sheet two, then the corresponding row is highlighted in sheet one.
how do i write into the code so that it highlights and then makes it
active
so you can see it on sheet two were the actal name is?

if you need to see the code that i have at the moment let me know and i
will
add it in.
thanks

mike

a little complicated programming, help would be appreciated!
 

Thanks for your input Bob, but I am at work, and things like that need to be
put through the Head of IT, and requires form filling.
Its annoying, because I wrote the code and managed to get it working apart
from this slight adjustment which I need to make.


"Bob Phillips" wrote:

You can get MySQL, which is a free database.

--

HTH

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


"mike" wrote in message
...
good morning all.

this is a little complicated, so ill start from the beginning....

and before you ask, i dont have access and cant get it, hence why i am
using
excel,

i have a workbook, there are two work sheets which contain various data,
but
ideally should be kept separate as they are for separate years.

i have written a macro which will search the sheets for a specific name or
reference number, then will highlight the row where that cell lies.

here is the problem...
if the file opens on sheet one and i search for a specific name which i
know
is on sheet two, then the corresponding row is highlighted in sheet one.
how do i write into the code so that it highlights and then makes it
active
so you can see it on sheet two were the actal name is?

if you need to see the code that i have at the moment let me know and i
will
add it in.
thanks

mike

a little complicated programming, help would be appreciated!
 

Hi Mike,

Try changing each instance of:

Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8

to:
.Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8

(note the prepended dot)

Without the qualification, the first statement refers to the active sheet.


I would also strongly advise you to explicitly declare all variables.

See Chip Pearson at:

http://www.cpearson.com/excel/variables.htm


---
Regards,
Norman


"mike" wrote in message
...
Good Question.... here is the code as it stands....

Sub auto_open()
Dim start, x, y, FR
start = "Do you wish to use the Search Tool?"
x = vbOKCancel + vbQuestion + vbDefaultButton1
y = "TITLE"
FR = MsgBox(start, x, y)
If FR = vbOK Then
Dim Msg, style, Title, Help, Ctxt, Response, MyString
Msg = "MESSAGE."
style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "TITLE"
Response = MsgBox(Msg, style, Title)
If Response = vbYes Then
Dim answer As String
answer = InputBox(INPUTBOX)
If answer = Empty Then
MsgBox Prompt:= MESSAGE
Else
With Worksheets("SHEET ONE").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET ONE").Range("M2:IV500")
d.Interior.ColorIndex = Null
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET TWO").Range("M2:IV500")
d.Interior.ColorIndex = Null
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End If
Else
Dim Cname As String
Cname = InputBox(INPUTBOX)
If Cname = Empty Then
MsgBox Prompt:=MESSAGE
Else
With Worksheets("SHEET ONE").Range("d4:d100")
Set z = .Find(Cname, LookIn:=xlValues)
If Not z Is Nothing Then
firstAddress = z.Address
Do
Rows(z.Row & ":" & z.Row).Interior.ColorIndex = 8
Set y = Worksheets("SHEET ONE").Range("M2:IV500")
y.Interior.ColorIndex = Null
Set z = .FindNext(z)
Loop While Not z Is Nothing And z.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("d2:d100")
Set w = .Find(Cname, LookIn:=xlValues)
If Not w Is Nothing Then
firstAddress = w.Address
Do
Rows(w.Row & ":" & w.Row).Interior.ColorIndex = 8
Set v = Worksheets("SHEET TWO").Range("M2:IV500")
v.Interior.ColorIndex = Null
Set w = .FindNext(w)
Loop While Not w Is Nothing And w.Address < firstAddress
End If
End With
End If
End If
End If
End Sub

Thanks Norman, hope you can help.

Mike


"Norman Jones" wrote:

Hi Mike,

How is the macro to know which sheet should be searched?

---
Regards,
Norman



"mike" wrote in message
...
good morning all.

this is a little complicated, so ill start from the beginning....

and before you ask, i dont have access and cant get it, hence why i am
using
excel,

i have a workbook, there are two work sheets which contain various
data,
but
ideally should be kept separate as they are for separate years.

i have written a macro which will search the sheets for a specific name
or
reference number, then will highlight the row where that cell lies.

here is the problem...
if the file opens on sheet one and i search for a specific name which i
know
is on sheet two, then the corresponding row is highlighted in sheet
one.
how do i write into the code so that it highlights and then makes it
active
so you can see it on sheet two were the actal name is?

if you need to see the code that i have at the moment let me know and i
will
add it in.
thanks

mike

a little complicated programming, help would be appreciated!
 

Appropriate corrections have been made:

There is very little in Excel that supports NULL. While it may have worked,
I changed the colorindex assignment to xlNone which is the proper constants.

You problem is you are not qualifying ROWS. So they refer to the active
sheet. I have added qualifiers.

Sub auto_open()
Dim start, x, y, FR
Dim sh1 as Worksheet, sh2 as worksheet
set sh1 = Worksheets("SHEET ONE")
set sh2 = Worksheets("SHEET TWO"
start = "Do you wish to use the Search Tool?"
x = vbOKCancel + vbQuestion + vbDefaultButton1
y = "TITLE"
FR = MsgBox(start, x, y)
If FR = vbOK Then
Dim Msg, style, Title, Help, Ctxt, Response, MyString
Msg = "MESSAGE."
style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "TITLE"
Response = MsgBox(Msg, style, Title)
If Response = vbYes Then
Dim answer As String
answer = InputBox(INPUTBOX)
If answer = Empty Then
MsgBox Prompt:= MESSAGE
Else
With Worksheets("SHEET ONE").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
sh1.Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET ONE").Range("M2:IV500")
d.Interior.ColorIndex = xlNone
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
sh2.Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET TWO").Range("M2:IV500")
d.Interior.ColorIndex = xlNone
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End If
Else
Dim Cname As String
Cname = InputBox(INPUTBOX)
If Cname = Empty Then
MsgBox Prompt:=MESSAGE
Else
With Worksheets("SHEET ONE").Range("d4:d100")
Set z = .Find(Cname, LookIn:=xlValues)
If Not z Is Nothing Then
firstAddress = z.Address
Do
sh1.Rows(z.Row & ":" & z.Row).Interior.ColorIndex = 8
Set y = Worksheets("SHEET ONE").Range("M2:IV500")
y.Interior.ColorIndex = xlNone
Set z = .FindNext(z)
Loop While Not z Is Nothing And z.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("d2:d100")
Set w = .Find(Cname, LookIn:=xlValues)
If Not w Is Nothing Then
firstAddress = w.Address
Do
sh2.Rows(w.Row & ":" & w.Row).Interior.ColorIndex = 8
Set v = Worksheets("SHEET TWO").Range("M2:IV500")
v.Interior.ColorIndex = xlNone
Set w = .FindNext(w)
Loop While Not w Is Nothing And w.Address < firstAddress
End If
End With
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy

"mike" wrote in message
...
Good Question.... here is the code as it stands....

Sub auto_open()
Dim start, x, y, FR
start = "Do you wish to use the Search Tool?"
x = vbOKCancel + vbQuestion + vbDefaultButton1
y = "TITLE"
FR = MsgBox(start, x, y)
If FR = vbOK Then
Dim Msg, style, Title, Help, Ctxt, Response, MyString
Msg = "MESSAGE."
style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "TITLE"
Response = MsgBox(Msg, style, Title)
If Response = vbYes Then
Dim answer As String
answer = InputBox(INPUTBOX)
If answer = Empty Then
MsgBox Prompt:= MESSAGE
Else
With Worksheets("SHEET ONE").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET ONE").Range("M2:IV500")
d.Interior.ColorIndex = Null
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET TWO").Range("M2:IV500")
d.Interior.ColorIndex = Null
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End If
Else
Dim Cname As String
Cname = InputBox(INPUTBOX)
If Cname = Empty Then
MsgBox Prompt:=MESSAGE
Else
With Worksheets("SHEET ONE").Range("d4:d100")
Set z = .Find(Cname, LookIn:=xlValues)
If Not z Is Nothing Then
firstAddress = z.Address
Do
Rows(z.Row & ":" & z.Row).Interior.ColorIndex = 8
Set y = Worksheets("SHEET ONE").Range("M2:IV500")
y.Interior.ColorIndex = Null
Set z = .FindNext(z)
Loop While Not z Is Nothing And z.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("d2:d100")
Set w = .Find(Cname, LookIn:=xlValues)
If Not w Is Nothing Then
firstAddress = w.Address
Do
Rows(w.Row & ":" & w.Row).Interior.ColorIndex = 8
Set v = Worksheets("SHEET TWO").Range("M2:IV500")
v.Interior.ColorIndex = Null
Set w = .FindNext(w)
Loop While Not w Is Nothing And w.Address < firstAddress
End If
End With
End If
End If
End If
End Sub

Thanks Norman, hope you can help.

Mike


"Norman Jones" wrote:

Hi Mike,

How is the macro to know which sheet should be searched?

---
Regards,
Norman



"mike" wrote in message
...
good morning all.

this is a little complicated, so ill start from the beginning....

and before you ask, i dont have access and cant get it, hence why i am
using
excel,

i have a workbook, there are two work sheets which contain various
data,
but
ideally should be kept separate as they are for separate years.

i have written a macro which will search the sheets for a specific
name or
reference number, then will highlight the row where that cell lies.

here is the problem...
if the file opens on sheet one and i search for a specific name which
i
know
is on sheet two, then the corresponding row is highlighted in sheet
one.
how do i write into the code so that it highlights and then makes it
active
so you can see it on sheet two were the actal name is?

if you need to see the code that i have at the moment let me know and
i
will
add it in.
thanks

mike

a little complicated programming, help would be appreciated!
 

Shame. I would have thought that any organisation that benefits from you
writing code, would see the advantage of having Access. In other words, if
they allow code at all why not allow Access?

--

HTH

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


"mike" wrote in message
...
Thanks for your input Bob, but I am at work, and things like that need to
be
put through the Head of IT, and requires form filling.
Its annoying, because I wrote the code and managed to get it working apart
from this slight adjustment which I need to make.


"Bob Phillips" wrote:

You can get MySQL, which is a free database.

--

HTH

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


"mike" wrote in message
...
good morning all.

this is a little complicated, so ill start from the beginning....

and before you ask, i dont have access and cant get it, hence why i am
using
excel,

i have a workbook, there are two work sheets which contain various
data,
but
ideally should be kept separate as they are for separate years.

i have written a macro which will search the sheets for a specific
name or
reference number, then will highlight the row where that cell lies.

here is the problem...
if the file opens on sheet one and i search for a specific name which
i
know
is on sheet two, then the corresponding row is highlighted in sheet
one.
how do i write into the code so that it highlights and then makes it
active
so you can see it on sheet two were the actal name is?

if you need to see the code that i have at the moment let me know and
i
will
add it in.
thanks

mike

a little complicated programming, help would be appreciated!
 

Thanks Tom.

I'll let you know how it goes.

"Tom Ogilvy" wrote:

Appropriate corrections have been made:

There is very little in Excel that supports NULL. While it may have worked,
I changed the colorindex assignment to xlNone which is the proper constants.

You problem is you are not qualifying ROWS. So they refer to the active
sheet. I have added qualifiers.

Sub auto_open()
Dim start, x, y, FR
Dim sh1 as Worksheet, sh2 as worksheet
set sh1 = Worksheets("SHEET ONE")
set sh2 = Worksheets("SHEET TWO"
start = "Do you wish to use the Search Tool?"
x = vbOKCancel + vbQuestion + vbDefaultButton1
y = "TITLE"
FR = MsgBox(start, x, y)
If FR = vbOK Then
Dim Msg, style, Title, Help, Ctxt, Response, MyString
Msg = "MESSAGE."
style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "TITLE"
Response = MsgBox(Msg, style, Title)
If Response = vbYes Then
Dim answer As String
answer = InputBox(INPUTBOX)
If answer = Empty Then
MsgBox Prompt:= MESSAGE
Else
With Worksheets("SHEET ONE").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
sh1.Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET ONE").Range("M2:IV500")
d.Interior.ColorIndex = xlNone
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
sh2.Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET TWO").Range("M2:IV500")
d.Interior.ColorIndex = xlNone
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End If
Else
Dim Cname As String
Cname = InputBox(INPUTBOX)
If Cname = Empty Then
MsgBox Prompt:=MESSAGE
Else
With Worksheets("SHEET ONE").Range("d4:d100")
Set z = .Find(Cname, LookIn:=xlValues)
If Not z Is Nothing Then
firstAddress = z.Address
Do
sh1.Rows(z.Row & ":" & z.Row).Interior.ColorIndex = 8
Set y = Worksheets("SHEET ONE").Range("M2:IV500")
y.Interior.ColorIndex = xlNone
Set z = .FindNext(z)
Loop While Not z Is Nothing And z.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("d2:d100")
Set w = .Find(Cname, LookIn:=xlValues)
If Not w Is Nothing Then
firstAddress = w.Address
Do
sh2.Rows(w.Row & ":" & w.Row).Interior.ColorIndex = 8
Set v = Worksheets("SHEET TWO").Range("M2:IV500")
v.Interior.ColorIndex = xlNone
Set w = .FindNext(w)
Loop While Not w Is Nothing And w.Address < firstAddress
End If
End With
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy

"mike" wrote in message
...
Good Question.... here is the code as it stands....

Sub auto_open()
Dim start, x, y, FR
start = "Do you wish to use the Search Tool?"
x = vbOKCancel + vbQuestion + vbDefaultButton1
y = "TITLE"
FR = MsgBox(start, x, y)
If FR = vbOK Then
Dim Msg, style, Title, Help, Ctxt, Response, MyString
Msg = "MESSAGE."
style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "TITLE"
Response = MsgBox(Msg, style, Title)
If Response = vbYes Then
Dim answer As String
answer = InputBox(INPUTBOX)
If answer = Empty Then
MsgBox Prompt:= MESSAGE
Else
With Worksheets("SHEET ONE").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET ONE").Range("M2:IV500")
d.Interior.ColorIndex = Null
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET TWO").Range("M2:IV500")
d.Interior.ColorIndex = Null
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End If
Else
Dim Cname As String
Cname = InputBox(INPUTBOX)
If Cname = Empty Then
MsgBox Prompt:=MESSAGE
Else
With Worksheets("SHEET ONE").Range("d4:d100")
Set z = .Find(Cname, LookIn:=xlValues)
If Not z Is Nothing Then
firstAddress = z.Address
Do
Rows(z.Row & ":" & z.Row).Interior.ColorIndex = 8
Set y = Worksheets("SHEET ONE").Range("M2:IV500")
y.Interior.ColorIndex = Null
Set z = .FindNext(z)
Loop While Not z Is Nothing And z.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("d2:d100")
Set w = .Find(Cname, LookIn:=xlValues)
If Not w Is Nothing Then
firstAddress = w.Address
Do
Rows(w.Row & ":" & w.Row).Interior.ColorIndex = 8
Set v = Worksheets("SHEET TWO").Range("M2:IV500")
v.Interior.ColorIndex = Null
Set w = .FindNext(w)
Loop While Not w Is Nothing And w.Address < firstAddress
End If
End With
End If
End If
End If
End Sub

Thanks Norman, hope you can help.

Mike


"Norman Jones" wrote:

Hi Mike,

How is the macro to know which sheet should be searched?

---
Regards,
Norman



"mike" wrote in message
...
good morning all.

this is a little complicated, so ill start from the beginning....

and before you ask, i dont have access and cant get it, hence why i am
using
excel,

i have a workbook, there are two work sheets which contain various
data,
but
ideally should be kept separate as they are for separate years.

i have written a macro which will search the sheets for a specific
name or
reference number, then will highlight the row where that cell lies.

here is the problem...
if the file opens on sheet one and i search for a specific name which
i
know
is on sheet two, then the corresponding row is highlighted in sheet
one.
how do i write into the code so that it highlights and then makes it
active
so you can see it on sheet two were the actal name is?

if you need to see the code that i have at the moment let me know and
i
will
add it in.
thanks

mike

a little complicated programming, help would be appreciated!
 

Tom,
Your adjustment in the code had worked (never doubted you!) but do you know
how i can also write into it so that the highlighted section becomes active,
so i dont have to search the sheets to see if a name has been highlighted?

"Tom Ogilvy" wrote:

Appropriate corrections have been made:

There is very little in Excel that supports NULL. While it may have worked,
I changed the colorindex assignment to xlNone which is the proper constants.

You problem is you are not qualifying ROWS. So they refer to the active
sheet. I have added qualifiers.

Sub auto_open()
Dim start, x, y, FR
Dim sh1 as Worksheet, sh2 as worksheet
set sh1 = Worksheets("SHEET ONE")
set sh2 = Worksheets("SHEET TWO"
start = "Do you wish to use the Search Tool?"
x = vbOKCancel + vbQuestion + vbDefaultButton1
y = "TITLE"
FR = MsgBox(start, x, y)
If FR = vbOK Then
Dim Msg, style, Title, Help, Ctxt, Response, MyString
Msg = "MESSAGE."
style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "TITLE"
Response = MsgBox(Msg, style, Title)
If Response = vbYes Then
Dim answer As String
answer = InputBox(INPUTBOX)
If answer = Empty Then
MsgBox Prompt:= MESSAGE
Else
With Worksheets("SHEET ONE").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
sh1.Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET ONE").Range("M2:IV500")
d.Interior.ColorIndex = xlNone
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
sh2.Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET TWO").Range("M2:IV500")
d.Interior.ColorIndex = xlNone
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End If
Else
Dim Cname As String
Cname = InputBox(INPUTBOX)
If Cname = Empty Then
MsgBox Prompt:=MESSAGE
Else
With Worksheets("SHEET ONE").Range("d4:d100")
Set z = .Find(Cname, LookIn:=xlValues)
If Not z Is Nothing Then
firstAddress = z.Address
Do
sh1.Rows(z.Row & ":" & z.Row).Interior.ColorIndex = 8
Set y = Worksheets("SHEET ONE").Range("M2:IV500")
y.Interior.ColorIndex = xlNone
Set z = .FindNext(z)
Loop While Not z Is Nothing And z.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("d2:d100")
Set w = .Find(Cname, LookIn:=xlValues)
If Not w Is Nothing Then
firstAddress = w.Address
Do
sh2.Rows(w.Row & ":" & w.Row).Interior.ColorIndex = 8
Set v = Worksheets("SHEET TWO").Range("M2:IV500")
v.Interior.ColorIndex = xlNone
Set w = .FindNext(w)
Loop While Not w Is Nothing And w.Address < firstAddress
End If
End With
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy

"mike" wrote in message
...
Good Question.... here is the code as it stands....

Sub auto_open()
Dim start, x, y, FR
start = "Do you wish to use the Search Tool?"
x = vbOKCancel + vbQuestion + vbDefaultButton1
y = "TITLE"
FR = MsgBox(start, x, y)
If FR = vbOK Then
Dim Msg, style, Title, Help, Ctxt, Response, MyString
Msg = "MESSAGE."
style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "TITLE"
Response = MsgBox(Msg, style, Title)
If Response = vbYes Then
Dim answer As String
answer = InputBox(INPUTBOX)
If answer = Empty Then
MsgBox Prompt:= MESSAGE
Else
With Worksheets("SHEET ONE").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET ONE").Range("M2:IV500")
d.Interior.ColorIndex = Null
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET TWO").Range("M2:IV500")
d.Interior.ColorIndex = Null
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End If
Else
Dim Cname As String
Cname = InputBox(INPUTBOX)
If Cname = Empty Then
MsgBox Prompt:=MESSAGE
Else
With Worksheets("SHEET ONE").Range("d4:d100")
Set z = .Find(Cname, LookIn:=xlValues)
If Not z Is Nothing Then
firstAddress = z.Address
Do
Rows(z.Row & ":" & z.Row).Interior.ColorIndex = 8
Set y = Worksheets("SHEET ONE").Range("M2:IV500")
y.Interior.ColorIndex = Null
Set z = .FindNext(z)
Loop While Not z Is Nothing And z.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("d2:d100")
Set w = .Find(Cname, LookIn:=xlValues)
If Not w Is Nothing Then
firstAddress = w.Address
Do
Rows(w.Row & ":" & w.Row).Interior.ColorIndex = 8
Set v = Worksheets("SHEET TWO").Range("M2:IV500")
v.Interior.ColorIndex = Null
Set w = .FindNext(w)
Loop While Not w Is Nothing And w.Address < firstAddress
End If
End With
End If
End If
End If
End Sub

Thanks Norman, hope you can help.

Mike


"Norman Jones" wrote:

Hi Mike,

How is the macro to know which sheet should be searched?

---
Regards,
Norman



"mike" wrote in message
...
good morning all.

this is a little complicated, so ill start from the beginning....

and before you ask, i dont have access and cant get it, hence why i am
using
excel,

i have a workbook, there are two work sheets which contain various
data,
but
ideally should be kept separate as they are for separate years.

i have written a macro which will search the sheets for a specific
name or
reference number, then will highlight the row where that cell lies.

here is the problem...
if the file opens on sheet one and i search for a specific name which
i
know
is on sheet two, then the corresponding row is highlighted in sheet
one.
how do i write into the code so that it highlights and then makes it
active
so you can see it on sheet two were the actal name is?

if you need to see the code that i have at the moment let me know and
i
will
add it in.
thanks

mike

a little complicated programming, help would be appreciated!
 

Well I think they do have Access, some of my colleagues have it.
But its such a democracy, that I have to go through all the channels to get
it, and its not worth the hassle when I can do pretty much what I need to in
Excel.

"Bob Phillips" wrote:

Shame. I would have thought that any organisation that benefits from you
writing code, would see the advantage of having Access. In other words, if
they allow code at all why not allow Access?

--

HTH

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


"mike" wrote in message
...
Thanks for your input Bob, but I am at work, and things like that need to
be
put through the Head of IT, and requires form filling.
Its annoying, because I wrote the code and managed to get it working apart
from this slight adjustment which I need to make.


"Bob Phillips" wrote:

You can get MySQL, which is a free database.

--

HTH

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


"mike" wrote in message
...
good morning all.

this is a little complicated, so ill start from the beginning....

and before you ask, i dont have access and cant get it, hence why i am
using
excel,

i have a workbook, there are two work sheets which contain various
data,
but
ideally should be kept separate as they are for separate years.

i have written a macro which will search the sheets for a specific
name or
reference number, then will highlight the row where that cell lies.

here is the problem...
if the file opens on sheet one and i search for a specific name which
i
know
is on sheet two, then the corresponding row is highlighted in sheet
one.
how do i write into the code so that it highlights and then makes it
active
so you can see it on sheet two were the actal name is?

if you need to see the code that i have at the moment let me know and
i
will
add it in.
thanks

mike

a little complicated programming, help would be appreciated!
 

Sub auto_open()
Dim start, x, y, FR
Dim sh1 as Worksheet, sh2 as worksheet
set sh1 = Worksheets("SHEET ONE")
set sh2 = Worksheets("SHEET TWO"
start = "Do you wish to use the Search Tool?"
x = vbOKCancel + vbQuestion + vbDefaultButton1
y = "TITLE"
FR = MsgBox(start, x, y)
If FR = vbOK Then
Dim Msg, style, Title, Help, Ctxt, Response, MyString
Msg = "MESSAGE."
style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "TITLE"
Response = MsgBox(Msg, style, Title)
If Response = vbYes Then
Dim answer As String
answer = InputBox(INPUTBOX)
If answer = Empty Then
MsgBox Prompt:= MESSAGE
Else
With Worksheets("SHEET ONE").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
sh1.Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET ONE").Range("M2:IV500")
d.Interior.ColorIndex = xlNone
sh1.Activate
c.Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
sh2.Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET TWO").Range("M2:IV500")
d.Interior.ColorIndex = xlNone
sh2.Activate
c.Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End If
Else
Dim Cname As String
Cname = InputBox(INPUTBOX)
If Cname = Empty Then
MsgBox Prompt:=MESSAGE
Else
With Worksheets("SHEET ONE").Range("d4:d100")
Set z = .Find(Cname, LookIn:=xlValues)
If Not z Is Nothing Then
firstAddress = z.Address
Do
sh1.Rows(z.Row & ":" & z.Row).Interior.ColorIndex = 8
Set y = Worksheets("SHEET ONE").Range("M2:IV500")
y.Interior.ColorIndex = xlNone
sh1.Activate
z.Select
Set z = .FindNext(z)
Loop While Not z Is Nothing And z.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("d2:d100")
Set w = .Find(Cname, LookIn:=xlValues)
If Not w Is Nothing Then
firstAddress = w.Address
Do
sh2.Rows(w.Row & ":" & w.Row).Interior.ColorIndex = 8
Set v = Worksheets("SHEET TWO").Range("M2:IV500")
v.Interior.ColorIndex = xlNone
sh2.Activate
w.Select
Set w = .FindNext(w)
Loop While Not w Is Nothing And w.Address < firstAddress
End If
End With
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy

"mike" wrote in message
...
Tom,
Your adjustment in the code had worked (never doubted you!) but do you
know
how i can also write into it so that the highlighted section becomes
active,
so i dont have to search the sheets to see if a name has been highlighted?

"Tom Ogilvy" wrote:

Appropriate corrections have been made:

There is very little in Excel that supports NULL. While it may have
worked,
I changed the colorindex assignment to xlNone which is the proper
constants.

You problem is you are not qualifying ROWS. So they refer to the active
sheet. I have added qualifiers.

Sub auto_open()
Dim start, x, y, FR
Dim sh1 as Worksheet, sh2 as worksheet
set sh1 = Worksheets("SHEET ONE")
set sh2 = Worksheets("SHEET TWO"
start = "Do you wish to use the Search Tool?"
x = vbOKCancel + vbQuestion + vbDefaultButton1
y = "TITLE"
FR = MsgBox(start, x, y)
If FR = vbOK Then
Dim Msg, style, Title, Help, Ctxt, Response, MyString
Msg = "MESSAGE."
style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "TITLE"
Response = MsgBox(Msg, style, Title)
If Response = vbYes Then
Dim answer As String
answer = InputBox(INPUTBOX)
If answer = Empty Then
MsgBox Prompt:= MESSAGE
Else
With Worksheets("SHEET ONE").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
sh1.Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET ONE").Range("M2:IV500")
d.Interior.ColorIndex = xlNone
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
sh2.Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET TWO").Range("M2:IV500")
d.Interior.ColorIndex = xlNone
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End If
Else
Dim Cname As String
Cname = InputBox(INPUTBOX)
If Cname = Empty Then
MsgBox Prompt:=MESSAGE
Else
With Worksheets("SHEET ONE").Range("d4:d100")
Set z = .Find(Cname, LookIn:=xlValues)
If Not z Is Nothing Then
firstAddress = z.Address
Do
sh1.Rows(z.Row & ":" & z.Row).Interior.ColorIndex =
8
Set y = Worksheets("SHEET ONE").Range("M2:IV500")
y.Interior.ColorIndex = xlNone
Set z = .FindNext(z)
Loop While Not z Is Nothing And z.Address <
firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("d2:d100")
Set w = .Find(Cname, LookIn:=xlValues)
If Not w Is Nothing Then
firstAddress = w.Address
Do
sh2.Rows(w.Row & ":" & w.Row).Interior.ColorIndex = 8
Set v = Worksheets("SHEET TWO").Range("M2:IV500")
v.Interior.ColorIndex = xlNone
Set w = .FindNext(w)
Loop While Not w Is Nothing And w.Address < firstAddress
End If
End With
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy

"mike" wrote in message
...
Good Question.... here is the code as it stands....

Sub auto_open()
Dim start, x, y, FR
start = "Do you wish to use the Search Tool?"
x = vbOKCancel + vbQuestion + vbDefaultButton1
y = "TITLE"
FR = MsgBox(start, x, y)
If FR = vbOK Then
Dim Msg, style, Title, Help, Ctxt, Response, MyString
Msg = "MESSAGE."
style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "TITLE"
Response = MsgBox(Msg, style, Title)
If Response = vbYes Then
Dim answer As String
answer = InputBox(INPUTBOX)
If answer = Empty Then
MsgBox Prompt:= MESSAGE
Else
With Worksheets("SHEET ONE").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET ONE").Range("M2:IV500")
d.Interior.ColorIndex = Null
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("e2:e500")
Set c = .Find(answer, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row & ":" & c.Row).Interior.ColorIndex = 8
Set d = Worksheets("SHEET TWO").Range("M2:IV500")
d.Interior.ColorIndex = Null
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End If
Else
Dim Cname As String
Cname = InputBox(INPUTBOX)
If Cname = Empty Then
MsgBox Prompt:=MESSAGE
Else
With Worksheets("SHEET ONE").Range("d4:d100")
Set z = .Find(Cname, LookIn:=xlValues)
If Not z Is Nothing Then
firstAddress = z.Address
Do
Rows(z.Row & ":" & z.Row).Interior.ColorIndex = 8
Set y = Worksheets("SHEET ONE").Range("M2:IV500")
y.Interior.ColorIndex = Null
Set z = .FindNext(z)
Loop While Not z Is Nothing And z.Address <
firstAddress
End If
End With
With Worksheets("SHEET TWO").Range("d2:d100")
Set w = .Find(Cname, LookIn:=xlValues)
If Not w Is Nothing Then
firstAddress = w.Address
Do
Rows(w.Row & ":" & w.Row).Interior.ColorIndex = 8
Set v = Worksheets("SHEET TWO").Range("M2:IV500")
v.Interior.ColorIndex = Null
Set w = .FindNext(w)
Loop While Not w Is Nothing And w.Address < firstAddress
End If
End With
End If
End If
End If
End Sub

Thanks Norman, hope you can help.

Mike


"Norman Jones" wrote:

Hi Mike,

How is the macro to know which sheet should be searched?

---
Regards,
Norman



"mike" wrote in message
...
good morning all.

this is a little complicated, so ill start from the beginning....

and before you ask, i dont have access and cant get it, hence why
i am
using
excel,

i have a workbook, there are two work sheets which contain various
data,
but
ideally should be kept separate as they are for separate years.

i have written a macro which will search the sheets for a specific
name or
reference number, then will highlight the row where that cell
lies.

here is the problem...
if the file opens on sheet one and i search for a specific name
which
i
know
is on sheet two, then the corresponding row is highlighted in
sheet
one.
how do i write into the code so that it highlights and then makes
it
active
so you can see it on sheet two were the actal name is?

if you need to see the code that i have at the moment let me know
and
i
will
add it in.
thanks

mike