I have an Excel spreadsheet with multiple worksheets. I want to be able to
set up a macro within one of the worksheets that will allow me to do the
following:
Once I type in a barcode number in a cell (say b3 of worksheet 2) and click
on the macro button it will:

1 open another worksheet in the file (worksheet 1)
2 find the number from the active cell (b3 of worksheet 2)
3 highlight the entire row in worksheet 1 where the barcode number was found
4 turn the text in that row green
5 save the worksheet
6 go back to worksheet 2

I don't know any visual basic code. I have in the past used macros to do
generic steps, none that required it to find a number and do something with
it. My problem is that it does the action only to the row the initial macro
was recorded in.

Any help would be greatly appreciated.

try this

Rightclick the sheet 2 tab

Click view code

On the left hand side of the popped up window click the arrow

Click worksheet

A default macro will pop up something like this will pop up

Private Sub Worksheet_SelectionChange(ByVal Target As Range)



End Sub



Ignore this

On the right hand side part of the window click the arrow and click CHANGE

The macro like this will pop up



Private Sub Worksheet_Change(ByVal Target As Range)



End sub



Type or copy these codestatements

==================

Application.EnableEvents = False

Dim x

x = Target.Value

MsgBox x

With Worksheets("sheet1").UsedRange

Set cfind = .Find(what:=x, lookat:=xlWhole)

..Cells.Font.ColorIndex = xlAutomatic

If Not cfind Is Nothing Then

cfind.Cells.Font.ColorIndex = 4

Else

GoTo line1

End If

End With

Worksheets("sheet2").Activate

Application.DisplayAlerts = False

ThisWorkbook.Save

Application.EnableEvents = True

GoTo line2

line1:

MsgBox "the barcode is NOT avilable in sheet1"

line2:

MsgBox "macro is over"

===================================





"Kerri" wrote in message
...
I have an Excel spreadsheet with multiple worksheets. I want to be able to
set up a macro within one of the worksheets that will allow me to do the
following:
Once I type in a barcode number in a cell (say b3 of worksheet 2) and
click
on the macro button it will:

1 open another worksheet in the file (worksheet 1)
2 find the number from the active cell (b3 of worksheet 2)
3 highlight the entire row in worksheet 1 where the barcode number was
found
4 turn the text in that row green
5 save the worksheet
6 go back to worksheet 2

I don't know any visual basic code. I have in the past used macros to do
generic steps, none that required it to find a number and do something
with
it. My problem is that it does the action only to the row the initial
macro
was recorded in.

Any help would be greatly appreciated.

Thanks for your quick reply. I copied and pasted the code, but it turned the
line
...Cells.Font.ColorIndex = 4 red in the code window
and would not perform the action. I deleted one of the periods in front of
the word Cells and it would somewhat perform the action in that it did change
the barcode in sheet1 green, but not the entire row that contained the
barcode and it did not save sheet1 to keep it green. The next time I put in
a barcode number in sheet2 it changed the previous insert back to black and
the only barcode that was green was the one just inserted. Thanks so much
for your help, it is at least a start.

"R..VENKATARAMAN" wrote:

try this

Rightclick the sheet 2 tab

Click view code

On the left hand side of the popped up window click the arrow

Click worksheet

A default macro will pop up something like this will pop up

Private Sub Worksheet_SelectionChange(ByVal Target As Range)



End Sub



Ignore this

On the right hand side part of the window click the arrow and click CHANGE

The macro like this will pop up



Private Sub Worksheet_Change(ByVal Target As Range)



End sub



Type or copy these codestatements

==================

Application.EnableEvents = False

Dim x

x = Target.Value

MsgBox x

With Worksheets("sheet1").UsedRange

Set cfind = .Find(what:=x, lookat:=xlWhole)

..Cells.Font.ColorIndex = xlAutomatic

If Not cfind Is Nothing Then

cfind.Cells.Font.ColorIndex = 4

Else

GoTo line1

End If

End With

Worksheets("sheet2").Activate

Application.DisplayAlerts = False

ThisWorkbook.Save

Application.EnableEvents = True

GoTo line2

line1:

MsgBox "the barcode is NOT avilable in sheet1"

line2:

MsgBox "macro is over"

===================================





"Kerri" wrote in message
...
I have an Excel spreadsheet with multiple worksheets. I want to be able to
set up a macro within one of the worksheets that will allow me to do the
following:
Once I type in a barcode number in a cell (say b3 of worksheet 2) and
click
on the macro button it will:

1 open another worksheet in the file (worksheet 1)
2 find the number from the active cell (b3 of worksheet 2)
3 highlight the entire row in worksheet 1 where the barcode number was
found
4 turn the text in that row green
5 save the worksheet
6 go back to worksheet 2

I don't know any visual basic code. I have in the past used macros to do
generic steps, none that required it to find a number and do something
with
it. My problem is that it does the action only to the row the initial
macro
was recorded in.

Any help would be greatly appreciated.

Kerri

A typo

Should only be one period in the line

..Cells.Font.ColorIndex = 4 red


Gord Dibben MS Excel MVP

On Thu, 17 Aug 2006 17:03:01 -0700, Kerri
wrote:

Thanks for your quick reply. I copied and pasted the code, but it turned the
line
..Cells.Font.ColorIndex = 4 red in the code window
and would not perform the action. I deleted one of the periods in front of
the word Cells and it would somewhat perform the action in that it did change
the barcode in sheet1 green, but not the entire row that contained the
barcode and it did not save sheet1 to keep it green. The next time I put in
a barcode number in sheet2 it changed the previous insert back to black and
the only barcode that was green was the one just inserted. Thanks so much
for your help, it is at least a start.

"R..VENKATARAMAN" wrote:

try this

Rightclick the sheet 2 tab

Click view code

On the left hand side of the popped up window click the arrow

Click worksheet

A default macro will pop up something like this will pop up

Private Sub Worksheet_SelectionChange(ByVal Target As Range)



End Sub



Ignore this

On the right hand side part of the window click the arrow and click CHANGE

The macro like this will pop up



Private Sub Worksheet_Change(ByVal Target As Range)



End sub



Type or copy these codestatements

==================

Application.EnableEvents = False

Dim x

x = Target.Value

MsgBox x

With Worksheets("sheet1").UsedRange

Set cfind = .Find(what:=x, lookat:=xlWhole)

..Cells.Font.ColorIndex = xlAutomatic

If Not cfind Is Nothing Then

cfind.Cells.Font.ColorIndex = 4

Else

GoTo line1

End If

End With

Worksheets("sheet2").Activate

Application.DisplayAlerts = False

ThisWorkbook.Save

Application.EnableEvents = True

GoTo line2

line1:

MsgBox "the barcode is NOT avilable in sheet1"

line2:

MsgBox "macro is over"

===================================





"Kerri" wrote in message
...
I have an Excel spreadsheet with multiple worksheets. I want to be able to
set up a macro within one of the worksheets that will allow me to do the
following:
Once I type in a barcode number in a cell (say b3 of worksheet 2) and
click
on the macro button it will:

1 open another worksheet in the file (worksheet 1)
2 find the number from the active cell (b3 of worksheet 2)
3 highlight the entire row in worksheet 1 where the barcode number was
found
4 turn the text in that row green
5 save the worksheet
6 go back to worksheet 2

I don't know any visual basic code. I have in the past used macros to do
generic steps, none that required it to find a number and do something
with
it. My problem is that it does the action only to the row the initial
macro
was recorded in.

Any help would be greatly appreciated.