• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

Macro to query with a txt file and make all words bold

Hi,

I have a txt file which has some name/numbers.I want a macro which can read the txt file search that text in the excel and if found make all the text bold and color to red.

Regards
Sharath
0
bsharath
Asked:
bsharath
  • 14
  • 13
1 Solution
 
Hitesh ManglaniCommented:
Sub Macro1()
Dim FF As Integer, str1 As String
FF = FreeFile
Open "C:\mytext,txt" For Input As #FF
While EOF(FF)
    Line Input #FF, str1
    For i = 1 To Sheet1.UsedRange.Rows.Count
        For j = 1 To Sheet1.UsedRange.Columns.Count
           If Sheet1.Cells(i, j) = str1 Then
                Sheet1.Cells(i, j).Font.Bold = True
                Sheet1.Cells(i, j).Interior.Color = RGB(255, 0, 0)
            End If
        Next
   
   
    Next
Wend
Close FF
End Sub

0
 
bsharathAuthor Commented:
Macro with no errors and no results too.

0
 
Hitesh ManglaniCommented:
>Open "C:\mytext.txt" For Input As #FF

In this line you have to put your text file path
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
bsharathAuthor Commented:
Put input as "#name"

Am i correct
every line shoiuld have #
0
 
Hitesh ManglaniCommented:
I did not understand you, can you paste the macro u r using and the path where your text file is?
0
 
bsharathAuthor Commented:
This is the macro.

Sub Macro1()
Dim FF As Integer, str1 As String
FF = FreeFile
Open "C:\mytext.txt" For Input As #FF
While EOF(FF)
    Line Input #FF, str1
    For i = 1 To Sheet1.UsedRange.Rows.Count
        For j = 1 To Sheet1.UsedRange.Columns.Count
           If Sheet1.Cells(i, j) = str1 Then
                Sheet1.Cells(i, j).Font.Bold = True
                Sheet1.Cells(i, j).Interior.Color = RGB(255, 0, 0)
            End If
        Next
   
   
    Next
Wend
Close FF
End Sub

I have the txt file in C:\filename.txt
in the txt filei have

Sharath
Reddy
ramesh

And have Sharath
Reddy in the excel sheet 1.
0
 
Hitesh ManglaniCommented:
In that case change your macro to

Sub Macro1()
Dim FF As Integer, str1 As String
FF = FreeFile
Open "C:\filename.txt" For Input As #FF
While Not EOF(FF)
    Line Input #FF, str1
    For i = 1 To Sheet1.UsedRange.Rows.Count
        For j = 1 To Sheet1.UsedRange.Columns.Count
           If Sheet1.Cells(i, j) = str1 Then
                Sheet1.Cells(i, j).Font.Bold = True
                Sheet1.Cells(i, j).Interior.Color = RGB(255, 0, 0)
            End If
        Next
   
   
    Next
Wend
Close FF
End Sub
0
 
Hitesh ManglaniCommented:
Is it working fine now?
0
 
bsharathAuthor Commented:
I just checked.This is searching case sencitive.

I want the script to search even though the case is diffe.

Ex:
Sharath
sharath
SHARATH
SHarath
any thing it has to search

Will it search even though it is in any colum or row?
0
 
Hitesh ManglaniCommented:
Then use the UCase function as shown
Sub Macro1()
Dim FF As Integer, str1 As String
FF = FreeFile
Open "C:\filename.txt" For Input As #FF
While Not EOF(FF)
    Line Input #FF, str1
    For i = 1 To Sheet1.UsedRange.Rows.Count
        For j = 1 To Sheet1.UsedRange.Columns.Count
           If UCase(Sheet1.Cells(i, j)) = UCase(str1) Then
                Sheet1.Cells(i, j).Font.Bold = True
                Sheet1.Cells(i, j).Interior.Color = RGB(255, 0, 0)
            End If
        Next
   
    Next
Wend
Close FF
End Sub
0
 
bsharathAuthor Commented:
I get this error.

Run-time error '13':
Type mismatch

If the names in the file is sharathreddy or Sharath reddy
and in the excel i just have it as sharath will it search the text?
0
 
Hitesh ManglaniCommented:
The previous macro worked fine in my excel sheet
This is the macro which will search the text even if Sharath is in the excel file, and sharathreddy is in the text file
Sub Macro1()
Dim FF As Integer, str1 As String
FF = FreeFile
Open "C:\filename.txt" For Input As #FF
While Not EOF(FF)
    Line Input #FF, str1
    For i = 1 To Sheet1.UsedRange.Rows.Count
        For j = 1 To Sheet1.UsedRange.Columns.Count
           If InStr(1, UCase(str1), UCase(Sheet1.Cells(i, j)), vbTextCompare) > 0 Then
                Sheet1.Cells(i, j).Font.Bold = True
                Sheet1.Cells(i, j).Interior.Color = RGB(255, 0, 0)
            End If
        Next
   
    Next
Wend
Close FF
End Sub


0
 
bsharathAuthor Commented:
Can you send me a sample file
As the sheet name in my file is Rack servers
I changed the macro too. But get a compiling error
0
 
bsharathAuthor Commented:
Where should i run this
0
 
Hitesh ManglaniCommented:
sample file io will not be able to upload from my office but this macro can be placed in the ThisWorkBook module which you can get after Pressing Alt+F11 in the workbook to open the macro editor and then run this macro.
0
 
bsharathAuthor Commented:
The sheet name is rack servers.
When i run i get no error or output.

I have the name Sharath in the txt file and even in the excel but no results
0
 
Hitesh ManglaniCommented:
can you upload your file?
0
 
bsharathAuthor Commented:
0
 
Hitesh ManglaniCommented:
buddy i dont have excel 2007 :(, only excel 2003. Cant open the file.
0
 
bsharathAuthor Commented:
0
 
Hitesh ManglaniCommented:
i used ur file with the following macro it worked fine,
but the name sharath is not there in the file.
Sub Macro1()
Dim FF As Integer, str1 As String
FF = FreeFile
Open "C:\filename.txt" For Input As #FF
While Not EOF(FF)
    Line Input #FF, str1
    For i = 1 To Sheet1.UsedRange.Rows.Count
        For j = 1 To Sheet1.UsedRange.Columns.Count
           If InStr(1, UCase(str1), UCase(Sheet1.Cells(i, j)), vbTextCompare) > 0 And Sheet1.Cells(i, j) <> "" Then
                Sheet1.Cells(i, j).Font.Bold = True
                Sheet1.Cells(i, j).Interior.Color = RGB(255, 0, 0)
            End If
        Next
   
    Next
Wend
Close FF
End Sub

0
 
Hitesh ManglaniCommented:
And sharath please put such questions in MS Excel Zone only
0
 
bsharathAuthor Commented:
Ok sure hitesh

I get this error.
---------------------------
Microsoft Visual Basic
---------------------------
Run-time error '424':

Object required
---------------------------
OK   Help  
---------------------------

I just put name sharath in the excel sheet and ran the macro i got the error.
0
 
Hitesh ManglaniCommented:
i put the name sharath in the sheet and did not get any error, in which cell did you put it?

do you know how to debug a macro?
0
 
bsharathAuthor Commented:
In colum 3 "C"
No i dont know to debug :<
0
 
bsharathAuthor Commented:
Should change the sheet name.Or the sheet name in the macro?
0
 
Hitesh ManglaniCommented:
Yes try changing the sheetname and see the result
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 14
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now