Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 8th, 2009, 01:13 PM
Registered User
 
Join Date: Jun 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Find and change font of a word in an Excel cell

I am trying to search an Excel file for specific key words and change the font of just the word, so that it stands out. I currently have to search files that are 30,000 lines long for these key words and the only thing that I have been able to do is change the entire cell. I have a couple of macros that change the font of the words, but I can not figure out how to make them search the whole document.

Here is the macro that was given to me, but it will only do 1 cell at at time

Option Explicit

Sub xlCellTextMgmt( _
TargetCell As Range, _
TargetWord As String, _
Optional FirstOnly As Boolean = True, _
Optional FontName As String, _
Optional FontBold As Boolean, _
Optional FontSize As Variant, _
Optional FontColor As Variant)
'
'************************************************* ***************************************
' Title xlCellTextMgmt
' Target Application: MS Excel
' Function: reformats selected text within the target cell
' Limitations: no explicit checks for acceptable values, e.g., does not
' check to ensure that FontName is a currently supported
' font
' Passed Values
' TargetCell [input,range] the target cell containing the text to
' be reformatted
' TargetWord [input,string] the words in the target cell text that are
' to be reformatted. TargetWord can contain
' anything from a single character to several
' words or even the entire text of the target
' cell
' FirstOnly [input,boolean] a TRUE/FALSE flag indicating if the
' reformatting is to be done on ONLY the 1st
' instance of the target word (True) or on ALL
' instances (False) {Default = True}
' FontName [input,string] the name of the new font. Omit if the font
' is to be left unchanged
' FontBold [input,boolean] a TRUE/FALSE flag indicating if the target
' words should be BOLD. True ==> Bold. Omit
' if the text is to be left unchanged.
' FontSize [input,variant] the size of the new font. Omit if the size
' is to be left unchanged.
' FontColor [input,variant] the color of the new font. Can be one of
' the standard colors from the Excel palette or
' can be one of the standard "vbColors".
' Omit if the color is to be left unchanged.
'
'************************************************* ***************************************
'
'
Dim Start As Long

Start = 0
Do
'
' find the start of TargetWord in TargetCell.Text
' if TargetWord not found, exit
'
Start = InStr(Start + 1, TargetCell.Text, TargetWord)
If Start < 1 Then Exit Sub
'
' test for each font arguement, if present, apply appropriately
'
With TargetCell.Characters(Start, Len(TargetWord)).Font
If IsNull(FontName) = False Then .Name = FontName
If IsNull(FontBold) = False Then .Bold = FontBold
If IsNull(FontSize) = False Then .Size = FontSize
If IsNull(FontColor) = False Then .ColorIndex = FontColor
End With
'
' if request was for ONLY the first instance of TargetWord, exit
' otherwise, loop back and see if there are more instances
'
If FirstOnly = True Then Exit Sub
Loop

End Sub

Sub xlCellTextMgmt_Test()
'
'************************************************* ***************************************
' Title xlCellTextMgmt_Test
' Target Application: MS Excel
' Function: demostrates xlCellTextMgmt
' Limitations: this is a demonstration or test procedure and, thus, does
' not have the checking and verification of a hardened
' application
' Passed Values: None
'
'************************************************* ***************************************
'
'
Dim FirstOnly As Boolean
Dim FontColor As Variant
Dim FontName As String
Dim FontBold As Boolean
Dim FontSize As Long
Dim TargetCell As Range
Dim TargetWord As String
'
' set TargtetCell to the selected cell and test for no text
' if no text, exit
'
Set TargetCell = Selection.Cells(1)
If TargetCell.Text = "" Then
MsgBox "you must start with a cell containing some text.", vbOKOnly
Exit Sub
End If
'
' interact with user to obtain data eventually passed to xlCellTextMgmt
'
TargetWord = Application.InputBox(Prompt:="target word(s)" & vbCrLf & vbCrLf & _
"[target word(s) can be anything from a single character" & vbCrLf & _
" to multiple words to the entire text of the target cell]", Type:=2)
FirstOnly = Application.InputBox(Prompt:="reformat ONLY the first instance?" & _
vbCrLf & vbCrLf & _
"[if True, only the first instance of " & TargetWord & _
" will be reformatted]" & vbCrLf & _
"[if False, all instances of " & TargetWord & _
" will be reformatted]", Type:=4)
FontName = Application.InputBox(Prompt:="font name #", Type:=2)
FontBold = Application.InputBox(Prompt:="bold? (True or False]", Type:=4)
FontSize = Application.InputBox(Prompt:="font size", Type:=1)
FontColor = Application.InputBox(Prompt:="Target color #", Type:=1)
Call xlCellTextMgmt(TargetCell, TargetWord, FirstOnly, FontName, FontBold, _
FontSize, FontColor)
Set TargetCell = Nothing

End Sub
Reply With Quote
  #2 (permalink)  
Old June 9th, 2009, 01:53 PM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

That seems like a lot of effort for what you wish to accomplish. Below is some sample code I put together that processes a range of cells marking every instance. Modify it as needed to fit your needs or let me know if you need me to modify it for you:
Code:
Public Sub MarkPhrase()

'Asks for phrase to find then finds and marks within each cell everywhere it is found.
  Dim rCell As Range, sToFind As String, iSeek As Long
  sToFind = InputBox("Enter Word / Phrase To Mark", "Criteria Request")
  If sToFind = "" Then MsgBox "Word / Phrase Required But Not Entered", , "Invalid Entry"
  For Each rCell In Selection 'can be any range or explicit (i.e. Range("A1:G6") instead of Selection)
    iSeek = InStr(1, rCell.Value, sToFind)
    Do While iSeek > 0
      With rCell.Characters(iSeek, Len(sToFind)).Font
        .Name = "Arial"
        .Size = 14
        .Bold = True
        .Color = RGB(200, 200, 200)
      End With
      iSeek = InStr(iSeek + 1, rCell.Value, sToFind)
    Loop
  Next

End Sub
Hope this helps point you in the right direction.

Last edited by allenm; June 9th, 2009 at 01:57 PM.. Reason: Removed default text
Reply With Quote
  #3 (permalink)  
Old June 9th, 2009, 02:44 PM
Registered User
 
Join Date: Jun 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This works great!!

Just one question. Is there a way so that capitalization does not matter? ie Phone and phone are not the same words and I would have to run the macro twice.

Thank you for your help.
Reply With Quote
  #4 (permalink)  
Old June 9th, 2009, 03:43 PM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

By default comparisons are done binary (Case Sensitive). This can be changed for most VBA functions.

Change the line:
iSeek = InStr(1, rCell.Value, sToFind)
To This:
iSeek = InStr(1, rCell.Value, sToFind, vbTextCompare)

Also Change the line:
iSeek = InStr(iSeek + 1, rCell.Value, sToFind)
To This:
iSeek = InStr(iSeek + 1, rCell.Value, sToFind, vbTextCompare)
Reply With Quote
  #5 (permalink)  
Old June 9th, 2009, 03:58 PM
Registered User
 
Join Date: Jun 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you for your help. I changed the lines of code and the macro runs perfect.

This will save me tons of time in my job.

Thank you.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to change font using winspool FileFound Visual Studio 2005 0 July 9th, 2007 04:57 AM
Cell/Font Color in DataGrid. su C# 1 December 3rd, 2006 04:58 AM
Java should get Excel data when change in cell vipuldshah77 Pro JSP 1 August 29th, 2006 09:23 AM
Open Word Doc from Access - find, find next save donaldmaloney Access VBA 1 May 25th, 2005 11:09 AM
open, change a excel document from word kareltje Excel VBA 2 February 14th, 2005 11:23 AM



All times are GMT -4. The time now is 10:26 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.