Adding a macro?

**sodowarts** · 10-23-2012, 09:31 AM

First off, I'm a relative newbie. I found this macro(?) below and would like to add it to a worksheet because it is supposed to make it possible for me to highlight different words. To customize it to my needs:
1. where in the string do I add the words that I want highlighted
2. does it matter if I want to only highlight in one column
3. once customized how do I make it work on my worksheet.

Option Explicit
Sub HighlightCells()
    Dim Lookin As Range, ff As String
    Dim i As Long
    Dim Fnd As Variant
    Dim fCell As Range
    Dim ws As Worksheet
    Dim xitem As Variant
    
   ' Fnd = Array("default", "Google")
 Fnd = Array("Ad hoc", "Adaptable", "Adequate", "And/or", "Approximately", "As a minimum", "As applicable" _
     , "As appropriate", "As possible")

    For Each ws In Worksheets
        With ws
            For Each xitem In Fnd
                Set Lookin = .Cells.Find(xitem, Lookin:=xlValues, LookAt:=xlPart)
                If Not Lookin Is Nothing Then
                    ff = Lookin.Address
                    Do
                       Lookin.Characters(InStr(1, Lookin, xitem), Len(xitem)).Font.ColorIndex = 3
                        Set Lookin = .Cells.FindNext(Lookin)
                    Loop Until ff = Lookin.Address
                End If
                Set Lookin = Nothing
            Next
        End With
    Next

End Sub

Many thanks!

**patel45** · 10-23-2012, 02:31 PM

attach a sample file with before and after desired macro running

**sodowarts** · 10-23-2012, 02:34 PM

I haven't done anything yet. All I have is a worksheet that I'd like to apply it to. Maybe there's a simpler way to highlight two different words in one column.

**brynbaker** · 10-23-2012, 02:33 PM

Start the other way around. Open your file in Excel, press Alt+F11. VBA edito. Look at the left pane which should be a sort of tree representation of the elements. Right click the projec name, select Insert... module. Double click the new module. It's blank. Copy the code above and paste in the module.

Now you are ready to amend and test. The line starting Fn=Array... has a number of strings in quotes separated by commas. Insert new, change or delete existing stings for your selection of words. Press F5 - the macro will work.

**sodowarts** · 10-23-2012, 03:10 PM

Thank you brynbaker. It worked....accept when a cell's text included something like DIRECTOR OF SALES (sales was chosen to be one of the highlighted words) only DIREC was highlighted not the word sales. It would only do the first few letters like this in other similiar cells in which the chosen highlighted word was not the first word. Any ideas on how to fix this? Thank you!

**sodowarts** · 10-23-2012, 03:47 PM

Originally Posted by brynbaker

Start the other way around. Open your file in Excel, press Alt+F11. VBA edito. Look at the left pane which should be a sort of tree representation of the elements. Right click the projec name, select Insert... module. Double click the new module. It's blank. Copy the code above and paste in the module.

Now you are ready to amend and test. The line starting Fn=Array... has a number of strings in quotes separated by commas. Insert new, change or delete existing stings for your selection of words. Press F5 - the macro will work.

Thank you brynbaker. It worked....accept when a cell's text included something like DIRECTOR OF SALES (sales was chosen to be one of the highlighted words) only DIREC was highlighted not the word sales. It would only do the first few letters like this in other similiar cells in which the chosen highlighted word was not the first word. Any ideas on how to fix this? Thank you!

**brynbaker** · 10-24-2012, 12:35 AM

That's nice piece of code you found. The line that's giving you hassle is obviously

Lookin.Characters(InStr(1, Lookin, xitem), Len(xitem)).Font.ColorIndex = 3

but I can't fault it. It gets the length of highlight right and the start position should be correct with (InStr(1, Lookin, xitem).

You asked about single column use; the line to change is

Set Lookin = .Cells.Find(xitem, Lookin:=xlValues, LookAt:=xlPart)

and I think it would be to replace .Cells. with .columns(3). [put the column number you need].

**sodowarts** · 10-24-2012, 10:13 AM

So there is no way to change the way it is being highlighted?? (Don't forget -- I'm a xl newbie). Could you do me a favor and write out how the code should read to make the changes? Thank you!

Originally Posted by brynbaker

That's nice piece of code you found. The line that's giving you hassle is obviously

Lookin.Characters(InStr(1, Lookin, xitem), Len(xitem)).Font.ColorIndex = 3

but I can't fault it. It gets the length of highlight right and the start position should be correct with (InStr(1, Lookin, xitem).

You asked about single column use; the line to change is

Set Lookin = .Cells.Find(xitem, Lookin:=xlValues, LookAt:=xlPart)

and I think it would be to replace .Cells. with .columns(3). [put the column number you need].

**brynbaker** · 10-25-2012, 03:21 AM

To limit to one line:

Set Lookin = .Cells.Find(xitem, Lookin:=xlValues, LookAt:=xlPart)

I can't fix the highlighting. It could be made to highlight the offending cell - replace the following lines

 
                    Do
                       Lookin.Characters(InStr(1, Lookin, xitem), Len(xitem)).Font.ColorIndex = 3
                        Set Lookin = .Cells.FindNext(Lookin)
                    Loop Until ff = Lookin.Address

with

                 ff.interior.colorindex = 6

but that's not what you want, and the existing code should work anyway.
Best I can do, sorry.

Adding a macro?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Adding a macro?

Re: Adding a macro?

Re: Adding a macro?

Re: Adding a macro?

Re: Adding a macro?

Re: Adding a macro?

Re: Adding a macro?

Re: Adding a macro?

Re: Adding a macro?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions