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

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

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

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

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

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

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

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

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