Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Format text  in all Cells that contain a specific Word. Bold / Italics / Cell Colour

Posted on 2011-04-20
8
Medium Priority
?
493 Views
Last Modified: 2013-11-05
Hi all,

I  have a macro which i came across a few months ago, which i have altered to do what i wanted it to do....
Namely, a userform is launched from a menu drop down item.
On this userform is the following macro, which will look for the text specified in the textbox and make all words specified bold

This is the macro:

Sub BoldFont()
Dim iLoop As Integer
Dim rNa As Range
Dim i As Integer
Dim FindIt As String

'Userform textbox where the specific word is entered and searched for
FindIt = BoldForm.txtSearch.Value
iLoop = WorksheetFunction.CountIf(Columns(1), FindIt)
Set rNa = Range("A1")
For i = 1 To iLoop
Set rNa = Columns(1).Find(What:=FindIt, After:=rNa, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
'make the specific word bold
rNa.Font.Bold = True
Next i
End Sub

I used the same macro, but changed the options, so that it would format text in Bold, Italics and even colour text or colour cells with specific text.

My problem, is that it will only work on text found in Column A.

I have tried several ways of chopping up the code to get it to work across all columns and all rows, but my knowledge or lack there of, fails me everytime.

Can anyone please help me amend the macro so that it will look at all columns and rows for the specific text entered in the Userform textbox.

Many thanks.
0
Comment
Question by:vestanpance_uk
  • 4
  • 3
8 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1000 total points
ID: 35431241
Try this version:
Sub BoldFont()
   Dim rNa As Range
   Dim FindIt As String
   Dim strAddy As String
   'Userform textbox where the specific word is entered and searched for
   FindIt = BoldForm.txtSearch.Value
   Set rNa = ActiveSheet.UsedRange.Find(What:=FindIt, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, _
   SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
   If Not rNa Is Nothing Then
      strAddy = rNa.Address
      Do
         'make the specific word bold
         rNa.Font.Bold = True
         ' add any other formatting required here
         Set rNa = ActiveSheet.UsedRange.FindNext(rngna)
      Loop While rNa.Address <> strAddy
   End If
End Sub

Open in new window

0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35431297
You could also consider using conditional formatting. This way you don't need any code. See the attached file for an example.
Enter a value in cell B1 and all the cells containing this value will be highlighted.
Search.xlsx
0
 

Author Comment

by:vestanpance_uk
ID: 35431341
Hi Rorya,

Thanks for your input, unfortunately, it errored out on this line:

Set rNa = ActiveSheet.UsedRange.FindNext(rngna)

with the message:
Runtime error 1004
Unable to get the FindNext property of the range class


Nicobo,

Thanks for your input also. Conditional formatting would require that i input data into a spreadsheet, however what i'm trying to accomplish is having a menu tool bar with useful utilities, which can fire up a userform of choice with options such as 'making specific words bold , italic or coloured.

As i mentioned previously, i have used the macro (altered) to make words bold, italic and coloured so i was hoping to have the current code amended slightly so i still understand what it's doing (so i can use it for other applications) but have it look at the entire sheet rather than just the A Column which is what it's doing at the moment.

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35431490
Sorry - a typo:

Set rNa = ActiveSheet.UsedRange.FindNext(rna)

Open in new window

0
 

Author Comment

by:vestanpance_uk
ID: 35431520
Sadly, that didn't work neither Rorya.. :(

Now i'm getting a Runtime Error 13, with a Type Mismatch  on this line

   Set rNa = ActiveSheet.UsedRange.Find(What:=FindIt, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)

Thanks for your help thus so far....

On another note, i did notice your typo and thought to myself what is that doing?
Unfortunately, i never actually questioned changing it.... nvm... :)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35431549
Code works fine for me, but on the off chance you don't have  cell active, try:
   Set rNa = ActiveSheet.UsedRange.Find(What:=FindIt, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)

Open in new window

0
 

Assisted Solution

by:vestanpance_uk
vestanpance_uk earned 0 total points
ID: 35432037
That's the one Rorya, you cracked it...

The original code, didn't require you to have the cell active, just that the cell was in Column A.

Now the amended code will run on all Columns which is what i required, so thank you very much.

As a solution i will accept your first, but i will post a complete working solution here also for the benefit of others.

Fully working code.
Sub BoldFont()
   Dim rNa As Range
   Dim FindIt As String
   Dim strAddy As String
   'Userform textbox where the specific word is entered and searched for
   FindIt = BoldForm.txtSearch.Value
   Set rNa = ActiveSheet.UsedRange.Find(What:=FindIt, LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
   If Not rNa Is Nothing Then
      strAddy = rNa.Address
      Do
         'make the specific word bold
         rNa.Font.Bold = True
         ' add any other formatting required here
         Set rNa = ActiveSheet.UsedRange.FindNext(rna)
      Loop While rNa.Address <> strAddy
   End If
End Sub

Open in new window


Cheers
Daz
0
 

Author Closing Comment

by:vestanpance_uk
ID: 35458454
Rorya, assigned all points to you, but accepted my own solution as assisted solution for clarity of other users, as the full working code was there.

Thanks again for your assistance

Cheers
Daz
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question