Set up macro in Excel to choose active row?
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.
|