hightlighting rows
 

Hi,
I have an excel spreadsheet which consists of columns names, phone nos.,
address, etc.
How can i automate excel to highlight those rows that consists of the list
of names in a text file?
thanks

hightlighting rows
 

try advance filter
for criteria copy thelist of names in textbox into an excel range with the
column headings


Lynn wrote in message
...
Hi,
I have an excel spreadsheet which consists of columns names, phone nos.,
address, etc.
How can i automate excel to highlight those rows that consists of the list
of names in a text file?
thanks

hightlighting rows
 

Try conditional formatting. Look up 'Highlight cells that meet specific
conditions' in the Answer
wizard in help

--

HTH

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


"Lynn" wrote in message
...
Hi,
I have an excel spreadsheet which consists of columns names, phone nos.,
address, etc.
How can i automate excel to highlight those rows that consists of the list
of names in a text file?
thanks

hightlighting rows
 

Lynn,

Here is a vba method that should do what you want. Make a back up copy of
your workbook to experiment on. Add a command button and paste the code below
into the click event. It may require some tweaking depending on the structure
of your text file containing the names. If you want to test it to see what it
does, put this in a blank workbook. On sheet1 type 10 or 15 names in Column A
starting in Row 1. Add ficticuous data in columns B-K if you want (doesn't
matter). A-K is the default highlight unless you adjust the setting. Create a
text file containing a few of the names exactly as they appear in the cells.
Save the text file. Push the button. Browse up the text file and click OPEN.
It will only look for a name in one cell. If you have first name in one col
and last name in another, it won't work without alteration. Give it a shot.

Roy

Private Sub CommandButton1_Click()

'This procedure assumes that your text file contains
'ONLY names, exactly as they appear on your sheet
'The file is opened in sequential mode and commas
'will act as delimiters, in other words the name
'can be stored as "John Smith" or Smith John",
'but not as "Smith, John" or "John, Smith"

'Check the calibration settings below to coordinate
'the procedure to your sheet layout

Dim CalculationMode As Long
Dim OriginalPageBreakMode As Boolean
Dim UserFile As String, Customer As String
Dim ThisRow As Long, ThisColumn As Integer, LastRow As Integer
Dim NamesColumn As Integer, FirstColumn As Integer
Dim LastColumn As Integer
Dim NoErrors As Boolean, myBad As Integer

On Error GoTo ErrorHandler
NoErrors = False
With Application
CalculationMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
OriginalPageBreakMode = .DisplayPageBreaks
.DisplayPageBreaks = False
End With

'SETTINGS
NamesColumn = 1 'Set this to column you keep names in
FirstColumn = 1 'Set this to first column to be highlighted
LastColumn = 11 'Set this to last column to be highlighted
'END SETTINGS

LastRow = ActiveSheet.Cells(65536, NamesColumn).End(xlUp).Row 'find last
occupied row.
UserFile = Application.GetOpenFilename(, , "Select a file to open")
If UserFile = "False" Then Exit Sub
Open UserFile For Input As #1
While Not EOF(1)
Input #1, Customer
For ThisRow = 1 To LastRow
If Cells(ThisRow, NamesColumn).Value = Customer Then
For ThisColumn = FirstColumn To LastColumn
With Cells(ThisRow, ThisColumn)
.Interior.ColorIndex = 6 'Yellow
End With
Next
End If
Next
Wend
NoErrors = True
ErrorHandler:
Close 1
With Application
.Calculation = CalculationMode
.ScreenUpdating = True
End With
ActiveSheet.DisplayPageBreaks = OriginalPageBreakMode
If NoErrors = False Then
myBad = MsgBox("Procedure aborted with errors." & vbLf & vbLf & "Error"
& Str(Err) & " occurred in " & Err.Source & vbLf & Err.Description,
vbInformation, "Oh No!")
End If
On Error GoTo 0
End Sub




"Lynn" wrote:

Hi,
I have an excel spreadsheet which consists of columns names, phone nos.,
address, etc.
How can i automate excel to highlight those rows that consists of the list
of names in a text file?
thanks

hightlighting rows
 

As soon as I posted, I noticed an issue whereas if you hit Cancel in the File
Open Dialog, the procedure exits without resetting screen updating,
calculation mode and page breaks. Use this rearranged version to avoid that
inconvenience.

Roy

Private Sub CommandButton1_Click()

'This procedure assumes that your text file contains
'ONLY names, exactly as they appear on your sheet
'The file is opened in sequential mode and commas
'will act as delimiters, in other words the name
'can be stored as "John Smith" or Smith John",
'but not as "Smith, John" or "John, Smith"

'Check the calibration settings below to coordinate
'the procedure to your sheet layout
Dim OriginalPageBreakMode As Boolean
Dim UserFile As String, Customer As String
Dim ThisRow As Long, ThisColumn As Integer, LastRow As Integer
Dim NamesColumn As Integer, FirstColumn As Integer
Dim LastColumn As Integer
Dim NoErrors As Boolean, myBad As Integer

On Error GoTo ErrorHandler
NoErrors = False

LastRow = ActiveSheet.Cells(65536, NamesColumn).End(xlUp).Row 'find last
occupied row.
UserFile = Application.GetOpenFilename(, , "Select a file to open")
If UserFile = "False" Then Exit Sub

With Application
CalculationMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
OriginalPageBreakMode = .DisplayPageBreaks
.DisplayPageBreaks = False
End With

'SETTINGS
NamesColumn = 1 'Set this to column you keep names in
FirstColumn = 1 'Set this to first column to be highlighted
LastColumn = 11 'Set this to last column to be highlighted
'END SETTINGS

Open UserFile For Input As #1
While Not EOF(1)
Input #1, Customer
For ThisRow = 1 To LastRow
If Cells(ThisRow, NamesColumn).Value = Customer Then
For ThisColumn = FirstColumn To LastColumn
With Cells(ThisRow, ThisColumn)
.Interior.ColorIndex = 6 'Yellow
End With
Next
End If
Next
Wend
NoErrors = True
ErrorHandler:
Close 1
With Application
.Calculation = CalculationMode
.ScreenUpdating = True
End With
ActiveSheet.DisplayPageBreaks = OriginalPageBreakMode
If NoErrors = False Then
myBad = MsgBox("Procedure aborted with errors." & vbLf & vbLf & "Error"
& Str(Err) & " occurred in " & Err.Source & vbLf & Err.Description,
vbInformation, "Oh No!")
End If
On Error GoTo 0
End Sub

hightlighting rows
 

Hi,
when i run this macro it says:
Run-time error '1004'
Method 'Calculation' of object '_Application' failed


"Roy Wagner" wrote in message
...
As soon as I posted, I noticed an issue whereas if you hit Cancel in the
File
Open Dialog, the procedure exits without resetting screen updating,
calculation mode and page breaks. Use this rearranged version to avoid
that
inconvenience.

Roy

Private Sub CommandButton1_Click()

'This procedure assumes that your text file contains
'ONLY names, exactly as they appear on your sheet
'The file is opened in sequential mode and commas
'will act as delimiters, in other words the name
'can be stored as "John Smith" or Smith John",
'but not as "Smith, John" or "John, Smith"

'Check the calibration settings below to coordinate
'the procedure to your sheet layout
Dim OriginalPageBreakMode As Boolean
Dim UserFile As String, Customer As String
Dim ThisRow As Long, ThisColumn As Integer, LastRow As Integer
Dim NamesColumn As Integer, FirstColumn As Integer
Dim LastColumn As Integer
Dim NoErrors As Boolean, myBad As Integer

On Error GoTo ErrorHandler
NoErrors = False

LastRow = ActiveSheet.Cells(65536, NamesColumn).End(xlUp).Row 'find last
occupied row.
UserFile = Application.GetOpenFilename(, , "Select a file to open")
If UserFile = "False" Then Exit Sub

With Application
CalculationMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
OriginalPageBreakMode = .DisplayPageBreaks
.DisplayPageBreaks = False
End With

'SETTINGS
NamesColumn = 1 'Set this to column you keep names in
FirstColumn = 1 'Set this to first column to be highlighted
LastColumn = 11 'Set this to last column to be highlighted
'END SETTINGS

Open UserFile For Input As #1
While Not EOF(1)
Input #1, Customer
For ThisRow = 1 To LastRow
If Cells(ThisRow, NamesColumn).Value = Customer Then
For ThisColumn = FirstColumn To LastColumn
With Cells(ThisRow, ThisColumn)
.Interior.ColorIndex = 6 'Yellow
End With
Next
End If
Next
Wend
NoErrors = True
ErrorHandler:
Close 1
With Application
.Calculation = CalculationMode
.ScreenUpdating = True
End With
ActiveSheet.DisplayPageBreaks = OriginalPageBreakMode
If NoErrors = False Then
myBad = MsgBox("Procedure aborted with errors." & vbLf & vbLf &
"Error"
& Str(Err) & " occurred in " & Err.Source & vbLf & Err.Description,
vbInformation, "Oh No!")
End If
On Error GoTo 0
End Sub

hightlighting rows
 

Roy apparently inadvertently got the SETTINGS code out of order on his
repost. The NamesColumn value needs to be defined before running the line:
LastRow = ActiveSheet.Cells(65536, NamesColumn).End(xlUp).Row

Try moving the following code to above the "LastRow = ActiveSheet(Cells..."
line:
'SETTINGS
NamesColumn = 1 'Set this to column you keep names in
FirstColumn = 1 'Set this to first column to be highlighted
LastColumn = 11 'Set this to last column to be highlighted
'END SETTINGS

Regards,
Greg


"Lynn" wrote:

Hi,
when i run this macro it says:
Run-time error '1004'
Method 'Calculation' of object '_Application' failed


"Roy Wagner" wrote in message
...
As soon as I posted, I noticed an issue whereas if you hit Cancel in the
File
Open Dialog, the procedure exits without resetting screen updating,
calculation mode and page breaks. Use this rearranged version to avoid
that
inconvenience.

Roy

Private Sub CommandButton1_Click()

'This procedure assumes that your text file contains
'ONLY names, exactly as they appear on your sheet
'The file is opened in sequential mode and commas
'will act as delimiters, in other words the name
'can be stored as "John Smith" or Smith John",
'but not as "Smith, John" or "John, Smith"

'Check the calibration settings below to coordinate
'the procedure to your sheet layout
Dim OriginalPageBreakMode As Boolean
Dim UserFile As String, Customer As String
Dim ThisRow As Long, ThisColumn As Integer, LastRow As Integer
Dim NamesColumn As Integer, FirstColumn As Integer
Dim LastColumn As Integer
Dim NoErrors As Boolean, myBad As Integer

On Error GoTo ErrorHandler
NoErrors = False

LastRow = ActiveSheet.Cells(65536, NamesColumn).End(xlUp).Row 'find last
occupied row.
UserFile = Application.GetOpenFilename(, , "Select a file to open")
If UserFile = "False" Then Exit Sub

With Application
CalculationMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
OriginalPageBreakMode = .DisplayPageBreaks
.DisplayPageBreaks = False
End With

'SETTINGS
NamesColumn = 1 'Set this to column you keep names in
FirstColumn = 1 'Set this to first column to be highlighted
LastColumn = 11 'Set this to last column to be highlighted
'END SETTINGS

Open UserFile For Input As #1
While Not EOF(1)
Input #1, Customer
For ThisRow = 1 To LastRow
If Cells(ThisRow, NamesColumn).Value = Customer Then
For ThisColumn = FirstColumn To LastColumn
With Cells(ThisRow, ThisColumn)
.Interior.ColorIndex = 6 'Yellow
End With
Next
End If
Next
Wend
NoErrors = True
ErrorHandler:
Close 1
With Application
.Calculation = CalculationMode
.ScreenUpdating = True
End With
ActiveSheet.DisplayPageBreaks = OriginalPageBreakMode
If NoErrors = False Then
myBad = MsgBox("Procedure aborted with errors." & vbLf & vbLf &
"Error"
& Str(Err) & " occurred in " & Err.Source & vbLf & Err.Description,
vbInformation, "Oh No!")
End If
On Error GoTo 0
End Sub

hightlighting rows
 

Thanks Greg,

When I was cutting and pasting the speed enhancement stuff, I inadvertenly
grabbed the settings in with the cut. I guess you'll have that at 5 in the
morning. It was a minor modification and I didn't retest. Sorry Lynn!

Roy


"Greg Wilson" wrote:

Roy apparently inadvertently got the SETTINGS code out of order on his
repost. The NamesColumn value needs to be defined before running the line:
LastRow = ActiveSheet.Cells(65536, NamesColumn).End(xlUp).Row

Try moving the following code to above the "LastRow = ActiveSheet(Cells..."
line:
'SETTINGS
NamesColumn = 1 'Set this to column you keep names in
FirstColumn = 1 'Set this to first column to be highlighted
LastColumn = 11 'Set this to last column to be highlighted
'END SETTINGS

Regards,
Greg


"Lynn" wrote:

Hi,
when i run this macro it says:
Run-time error '1004'
Method 'Calculation' of object '_Application' failed


"Roy Wagner" wrote in message
...
As soon as I posted, I noticed an issue whereas if you hit Cancel in the
File
Open Dialog, the procedure exits without resetting screen updating,
calculation mode and page breaks. Use this rearranged version to avoid
that
inconvenience.

Roy

Private Sub CommandButton1_Click()

'This procedure assumes that your text file contains
'ONLY names, exactly as they appear on your sheet
'The file is opened in sequential mode and commas
'will act as delimiters, in other words the name
'can be stored as "John Smith" or Smith John",
'but not as "Smith, John" or "John, Smith"

'Check the calibration settings below to coordinate
'the procedure to your sheet layout
Dim OriginalPageBreakMode As Boolean
Dim UserFile As String, Customer As String
Dim ThisRow As Long, ThisColumn As Integer, LastRow As Integer
Dim NamesColumn As Integer, FirstColumn As Integer
Dim LastColumn As Integer
Dim NoErrors As Boolean, myBad As Integer

On Error GoTo ErrorHandler
NoErrors = False

LastRow = ActiveSheet.Cells(65536, NamesColumn).End(xlUp).Row 'find last
occupied row.
UserFile = Application.GetOpenFilename(, , "Select a file to open")
If UserFile = "False" Then Exit Sub

With Application
CalculationMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
OriginalPageBreakMode = .DisplayPageBreaks
.DisplayPageBreaks = False
End With

'SETTINGS
NamesColumn = 1 'Set this to column you keep names in
FirstColumn = 1 'Set this to first column to be highlighted
LastColumn = 11 'Set this to last column to be highlighted
'END SETTINGS

Open UserFile For Input As #1
While Not EOF(1)
Input #1, Customer
For ThisRow = 1 To LastRow
If Cells(ThisRow, NamesColumn).Value = Customer Then
For ThisColumn = FirstColumn To LastColumn
With Cells(ThisRow, ThisColumn)
.Interior.ColorIndex = 6 'Yellow
End With
Next
End If
Next
Wend
NoErrors = True
ErrorHandler:
Close 1
With Application
.Calculation = CalculationMode
.ScreenUpdating = True
End With
ActiveSheet.DisplayPageBreaks = OriginalPageBreakMode
If NoErrors = False Then
myBad = MsgBox("Procedure aborted with errors." & vbLf & vbLf &
"Error"
& Str(Err) & " occurred in " & Err.Source & vbLf & Err.Description,
vbInformation, "Oh No!")
End If
On Error GoTo 0
End Sub

hightlighting rows
 

hi,
is there a way which i can know which are the names that are not in my list
too?
thanks

"Roy Wagner" wrote in message
...
Thanks Greg,

When I was cutting and pasting the speed enhancement stuff, I inadvertenly
grabbed the settings in with the cut. I guess you'll have that at 5 in the
morning. It was a minor modification and I didn't retest. Sorry Lynn!

Roy


"Greg Wilson" wrote:

Roy apparently inadvertently got the SETTINGS code out of order on his
repost. The NamesColumn value needs to be defined before running the
line:
LastRow = ActiveSheet.Cells(65536, NamesColumn).End(xlUp).Row

Try moving the following code to above the "LastRow =
ActiveSheet(Cells..."
line:
'SETTINGS
NamesColumn = 1 'Set this to column you keep names in
FirstColumn = 1 'Set this to first column to be highlighted
LastColumn = 11 'Set this to last column to be highlighted
'END SETTINGS

Regards,
Greg


"Lynn" wrote:

Hi,
when i run this macro it says:
Run-time error '1004'
Method 'Calculation' of object '_Application' failed


"Roy Wagner" wrote in message
...
As soon as I posted, I noticed an issue whereas if you hit Cancel in
the
File
Open Dialog, the procedure exits without resetting screen updating,
calculation mode and page breaks. Use this rearranged version to
avoid
that
inconvenience.

Roy

Private Sub CommandButton1_Click()

'This procedure assumes that your text file contains
'ONLY names, exactly as they appear on your sheet
'The file is opened in sequential mode and commas
'will act as delimiters, in other words the name
'can be stored as "John Smith" or Smith John",
'but not as "Smith, John" or "John, Smith"

'Check the calibration settings below to coordinate
'the procedure to your sheet layout
Dim OriginalPageBreakMode As Boolean
Dim UserFile As String, Customer As String
Dim ThisRow As Long, ThisColumn As Integer, LastRow As Integer
Dim NamesColumn As Integer, FirstColumn As Integer
Dim LastColumn As Integer
Dim NoErrors As Boolean, myBad As Integer

On Error GoTo ErrorHandler
NoErrors = False

LastRow = ActiveSheet.Cells(65536, NamesColumn).End(xlUp).Row 'find
last
occupied row.
UserFile = Application.GetOpenFilename(, , "Select a file to open")
If UserFile = "False" Then Exit Sub

With Application
CalculationMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
OriginalPageBreakMode = .DisplayPageBreaks
.DisplayPageBreaks = False
End With

'SETTINGS
NamesColumn = 1 'Set this to column you keep names in
FirstColumn = 1 'Set this to first column to be highlighted
LastColumn = 11 'Set this to last column to be highlighted
'END SETTINGS

Open UserFile For Input As #1
While Not EOF(1)
Input #1, Customer
For ThisRow = 1 To LastRow
If Cells(ThisRow, NamesColumn).Value = Customer Then
For ThisColumn = FirstColumn To LastColumn
With Cells(ThisRow, ThisColumn)
.Interior.ColorIndex = 6 'Yellow
End With
Next
End If
Next
Wend
NoErrors = True
ErrorHandler:
Close 1
With Application
.Calculation = CalculationMode
.ScreenUpdating = True
End With
ActiveSheet.DisplayPageBreaks = OriginalPageBreakMode
If NoErrors = False Then
myBad = MsgBox("Procedure aborted with errors." & vbLf & vbLf &
"Error"
& Str(Err) & " occurred in " & Err.Source & vbLf & Err.Description,
vbInformation, "Oh No!")
End If
On Error GoTo 0
End Sub

hightlighting rows
 

Greg ,
after adding this macro, when i go to tools - macro - macros , i don't see
CommandButton1_Click in the macro list.
how can i add it in the macro so that i can select and run it?
thanks

"Greg Wilson" wrote in message
...
Roy apparently inadvertently got the SETTINGS code out of order on his
repost. The NamesColumn value needs to be defined before running the line:
LastRow = ActiveSheet.Cells(65536, NamesColumn).End(xlUp).Row

Try moving the following code to above the "LastRow =
ActiveSheet(Cells..."
line:
'SETTINGS
NamesColumn = 1 'Set this to column you keep names in
FirstColumn = 1 'Set this to first column to be highlighted
LastColumn = 11 'Set this to last column to be highlighted
'END SETTINGS

Regards,
Greg


"Lynn" wrote:

Hi,
when i run this macro it says:
Run-time error '1004'
Method 'Calculation' of object '_Application' failed


"Roy Wagner" wrote in message
...
As soon as I posted, I noticed an issue whereas if you hit Cancel in
the
File
Open Dialog, the procedure exits without resetting screen updating,
calculation mode and page breaks. Use this rearranged version to avoid
that
inconvenience.

Roy

Private Sub CommandButton1_Click()

'This procedure assumes that your text file contains
'ONLY names, exactly as they appear on your sheet
'The file is opened in sequential mode and commas
'will act as delimiters, in other words the name
'can be stored as "John Smith" or Smith John",
'but not as "Smith, John" or "John, Smith"

'Check the calibration settings below to coordinate
'the procedure to your sheet layout
Dim OriginalPageBreakMode As Boolean
Dim UserFile As String, Customer As String
Dim ThisRow As Long, ThisColumn As Integer, LastRow As Integer
Dim NamesColumn As Integer, FirstColumn As Integer
Dim LastColumn As Integer
Dim NoErrors As Boolean, myBad As Integer

On Error GoTo ErrorHandler
NoErrors = False

LastRow = ActiveSheet.Cells(65536, NamesColumn).End(xlUp).Row 'find
last
occupied row.
UserFile = Application.GetOpenFilename(, , "Select a file to open")
If UserFile = "False" Then Exit Sub

With Application
CalculationMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
OriginalPageBreakMode = .DisplayPageBreaks
.DisplayPageBreaks = False
End With

'SETTINGS
NamesColumn = 1 'Set this to column you keep names in
FirstColumn = 1 'Set this to first column to be highlighted
LastColumn = 11 'Set this to last column to be highlighted
'END SETTINGS

Open UserFile For Input As #1
While Not EOF(1)
Input #1, Customer
For ThisRow = 1 To LastRow
If Cells(ThisRow, NamesColumn).Value = Customer Then
For ThisColumn = FirstColumn To LastColumn
With Cells(ThisRow, ThisColumn)
.Interior.ColorIndex = 6 'Yellow
End With
Next
End If
Next
Wend
NoErrors = True
ErrorHandler:
Close 1
With Application
.Calculation = CalculationMode
.ScreenUpdating = True
End With
ActiveSheet.DisplayPageBreaks = OriginalPageBreakMode
If NoErrors = False Then
myBad = MsgBox("Procedure aborted with errors." & vbLf & vbLf &
"Error"
& Str(Err) & " occurred in " & Err.Source & vbLf & Err.Description,
vbInformation, "Oh No!")
End If
On Error GoTo 0
End Sub

hightlighting rows
 

Lynn,

I built the procedure in the click event of a command button on sheet 1. By
default it was named Private Sub CommandBUtton1_Click. Get rid of the word
"Private", so that it is called Sub CommandButton1_Click, and it will be seen
from the Macro menu selection.

As far as reporting which names do not match. Do you mean that names in the
text file may not have a match in the work book? This is easy enough to deal
with. Do you want the unmatched names to appear in a list somewhere, and if
so, where? Sheet3? Let me know. If it's just a couple and you want to see a
messagebox list names one at a time, or just to let you know that there were
some unmatched names, please check sheet x, this is all fairly easy to add to
the existing procedure.

I am only guessing at how to read your names file without knowing it's
actual structure. Let me know if actually can read the names properly and if
not, I would need accurate details as to how that file is arranged in a
structural sense? I also gathered from your original post that first and last
names are in the same column. The procedure as a result is only looking at
one column to match up names. Let me know how it works out.

Roy


"Lynn" wrote:

Greg ,
after adding this macro, when i go to tools - macro - macros , i don't see
CommandButton1_Click in the macro list.
how can i add it in the macro so that i can select and run it?
thanks

"Greg Wilson" wrote in message
...
Roy apparently inadvertently got the SETTINGS code out of order on his
repost. The NamesColumn value needs to be defined before running the line:
LastRow = ActiveSheet.Cells(65536, NamesColumn).End(xlUp).Row

Try moving the following code to above the "LastRow =
ActiveSheet(Cells..."
line:
'SETTINGS
NamesColumn = 1 'Set this to column you keep names in
FirstColumn = 1 'Set this to first column to be highlighted
LastColumn = 11 'Set this to last column to be highlighted
'END SETTINGS

Regards,
Greg


"Lynn" wrote:

Hi,
when i run this macro it says:
Run-time error '1004'
Method 'Calculation' of object '_Application' failed


"Roy Wagner" wrote in message
...
As soon as I posted, I noticed an issue whereas if you hit Cancel in
the
File
Open Dialog, the procedure exits without resetting screen updating,
calculation mode and page breaks. Use this rearranged version to avoid
that
inconvenience.

Roy

Private Sub CommandButton1_Click()

'This procedure assumes that your text file contains
'ONLY names, exactly as they appear on your sheet
'The file is opened in sequential mode and commas
'will act as delimiters, in other words the name
'can be stored as "John Smith" or Smith John",
'but not as "Smith, John" or "John, Smith"

'Check the calibration settings below to coordinate
'the procedure to your sheet layout
Dim OriginalPageBreakMode As Boolean
Dim UserFile As String, Customer As String
Dim ThisRow As Long, ThisColumn As Integer, LastRow As Integer
Dim NamesColumn As Integer, FirstColumn As Integer
Dim LastColumn As Integer
Dim NoErrors As Boolean, myBad As Integer

On Error GoTo ErrorHandler
NoErrors = False

LastRow = ActiveSheet.Cells(65536, NamesColumn).End(xlUp).Row 'find
last
occupied row.
UserFile = Application.GetOpenFilename(, , "Select a file to open")
If UserFile = "False" Then Exit Sub

With Application
CalculationMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
OriginalPageBreakMode = .DisplayPageBreaks
.DisplayPageBreaks = False
End With

'SETTINGS
NamesColumn = 1 'Set this to column you keep names in
FirstColumn = 1 'Set this to first column to be highlighted
LastColumn = 11 'Set this to last column to be highlighted
'END SETTINGS

Open UserFile For Input As #1
While Not EOF(1)
Input #1, Customer
For ThisRow = 1 To LastRow
If Cells(ThisRow, NamesColumn).Value = Customer Then
For ThisColumn = FirstColumn To LastColumn
With Cells(ThisRow, ThisColumn)
.Interior.ColorIndex = 6 'Yellow
End With
Next
End If
Next
Wend
NoErrors = True
ErrorHandler:
Close 1
With Application
.Calculation = CalculationMode
.ScreenUpdating = True
End With
ActiveSheet.DisplayPageBreaks = OriginalPageBreakMode
If NoErrors = False Then
myBad = MsgBox("Procedure aborted with errors." & vbLf & vbLf &
"Error"
& Str(Err) & " occurred in " & Err.Source & vbLf & Err.Description,
vbInformation, "Oh No!")
End If
On Error GoTo 0
End Sub

hightlighting rows
 

By design, the Private key word prevents macros from appearing in the macro
list. Suggested is that you add it to a standard module and give it a
different name, e.g. "Sub XYZ()". This assumes you want to assign it either
to a control from the Forms toolbar, a toolbar button or just run it from the
macro list.

Roy had intended that you add a command button to the worksheet from the
Controls Toolbox toolbar. By default, the name of the first such command
button added would be "CommandButton1". The code was intended to be added to
the worksheet code module. The linkage between the code and the command
button would be automatic in this case - i.e. macro assignment not necessary.

Regards,
Greg

"Lynn" wrote:

Greg ,
after adding this macro, when i go to tools - macro - macros , i don't see
CommandButton1_Click in the macro list.
how can i add it in the macro so that i can select and run it?
thanks

hightlighting rows
 

guys,
it worked for me. thanks alot

"Greg Wilson" wrote in message
...
By design, the Private key word prevents macros from appearing in the
macro
list. Suggested is that you add it to a standard module and give it a
different name, e.g. "Sub XYZ()". This assumes you want to assign it
either
to a control from the Forms toolbar, a toolbar button or just run it from
the
macro list.

Roy had intended that you add a command button to the worksheet from the
Controls Toolbox toolbar. By default, the name of the first such command
button added would be "CommandButton1". The code was intended to be added
to
the worksheet code module. The linkage between the code and the command
button would be automatic in this case - i.e. macro assignment not
necessary.

Regards,
Greg

"Lynn" wrote:

Greg ,
after adding this macro, when i go to tools - macro - macros , i don't
see
CommandButton1_Click in the macro list.
how can i add it in the macro so that i can select and run it?
thanks