VBA code to highlight a specific text in cell

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819
Is there a VBA code if in collumn (A) has the word "Dog" then say it found it in (A16) then hilghlight the rows thru (A16 to G16). Conditional formatting wont work for what i'm asking for but I wont bore you for my reasons why, But I'll keep this short. Is there a VBA code for that?

Thanks!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
L

Legacy 98055

Guest
How do you know conditional formatting won't work. With what you have given so far, it will work using "Formula Is". Please bore me. :)

If no conditional formatting, will the word, "Dog" be found more than once in column A? If so, will each of these rows be highlighted?

Thanks,
Tom
 

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819
Because if there is a code I could do formating like changeing the font size and more options choosing borders conditional formatting only gives you so much.
Yes the "Dog" will appear moe than once and thoses rows should be highlighted.

Any Ideas?
 
L

Legacy 98055

Guest
Sure. I am reaching deep down to come up with something for you. :)

Please re-read my initial post and answer the questions.

Oh. I see that you did.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224

ADVERTISEMENT

Can you clarify please, your subject line says you want formatting applied to specific text, but you also say a couple times you want rows highlighted. What exactly do you want to format..."Dog", "dog", "dogma", "dogfood" etc, or the entire row where "Dog" or whatever is found, etc. And, is the key word housed in cell A16?
 

Bubbis Thedog

Well-known Member
Joined
Jul 29, 2004
Messages
967
Code:
Option Explicit

Sub Custom_Conditional_Formatting()

Application.ScreenUpdating = False

Dim Cell As Range

For Each Cell In Range("A:A")
  If Cell = "Dog" Then
   Cell.Interior.Color = vbYellow
   Cell.Offset(0, 1).Interior.Color = vbYellow
   Cell.Offset(0, 2).Interior.Color = vbYellow
   Cell.Offset(0, 3).Interior.Color = vbYellow
   Cell.Offset(0, 4).Interior.Color = vbYellow
   Cell.Offset(0, 4).Interior.Color = vbYellow
   Cell.Offset(0, 5).Interior.Color = vbYellow
   Cell.Offset(0, 6).Interior.Color = vbYellow
  End If
Next Cell

Application.ScreenUpdating = True

End Sub

That's an awful way to highlight the cells in the rows with "Dog" in column A, but see if that accomplishes what you want it to. If it does, then the more qualified peeps on here can modify my code.

HTH
 

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819

ADVERTISEMENT

I transfer data into excel so if there is a code I would run that code then if it find the word "Dog" in collumn (A) just say it was in (A5) the code will highlight row thru (A5 to G5). My data changes daily so the word "Dog" wont be in the same place in the same collumn But not in the same cell. Can the code have multiple search so I coild add to the code. Meaninng Can the the code search for words "Dog" and "Cat" then highlight that specific row where it finds "Dog" and "Cat"

Say it found "Dog" in Collumn (A200) highlight the row thru (A200 to G200)
and if found "Cat" in collumn (A20) highlight the row thru (A20 to G20).

So code have to highlight NOT colored but highlight so If the cell are highlighted then I could record a macro do the following change the font size put specific borders or colored borders ect. conditional formatting only does so much.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
One way if I understand you correctly...

First, use column H to enter the multiple values you want to search for. Example, in H1 enter Dog, in H2 enter Cat, etc.

This macro will do what you say you want, assuming...

1) You only want to find one or the first instance of each value.
2) Your keywords (Dog, Cat, etc) are case sensitive (if not, change the True argument in the macro to for MatchCase to False).

Post back if there are multiple instances of Dog or Cat or whatever in column A; I didn't want to post more code than you need.

This macro will hilite the found rows from A:G in yellow.



Sub Test1()
Application.ScreenUpdating = False
Dim cell As Range, fWhat As String, fRow As Variant
Cells.Interior.ColorIndex = 0
For Each cell In Columns(8).SpecialCells(2)
fWhat = cell.Value
Set fRow = Columns(1).Find(What:=fWhat, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=True)
If Not fRow Is Nothing Then Range(Cells(fRow.row, 1), Cells(fRow.row, 7)).Interior.ColorIndex = 6
Next cell
Application.ScreenUpdating = True
End Sub
 
L

Legacy 98055

Guest
Create a reusable function that returns the rows containing your search term from within a given range. Then process the rows yourself. This example will search for all occurances of "dog" in column A, will return the rows as an array, will highlight each found row in yellow, and will bold the search term. In this case, "dog".


Code:
Sub Example()
    Dim RowsWithDog() As Long, x As Long, SrchTerm As Variant, SrchRange As Range
    
    SrchTerm = "dog"
    Set SrchRange = Range("A:A")
    
    RowsWithDog() = FindDoggieRows(SrchRange, SrchTerm, xlPart)
    
    For x = 0 To UBound(RowsWithDog)
        'process your rows here
        With Range("A" & RowsWithDog(x))
            .Characters(InStr(.Value, SrchTerm), Len(CStr(SrchTerm))).Font.FontStyle = "Bold"
            .Resize(, 7).Interior.ColorIndex = 6
        End With
    Next
    
End Sub

Public Function FindDoggieRows(rng As Range, Value As Variant, LookAt As XlLookAt) As Long()
    Dim c As Range, d() As Long, x As Long, firstAddress As String
    ReDim d(0)
    With rng
        Set c = .Find(Value, , xlValues, LookAt)
        If Not c Is Nothing Then
            d(0) = c.Row
            firstAddress = c.Address
            Do
                Set c = .FindNext(c)
                If (Not c Is Nothing) And c.Address <> firstAddress Then
                    x = x + 1
                    ReDim Preserve d(x)
                    d(x) = c.Row
                End If
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
    FindDoggieRows = d
End Function

Change...

SrchTerm = "dog"
SrchRange = Range("A:A")

...to suit.

Download example here if you have any problems.


Tom
 

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819
Can either codes you guys provided just highlight using that function in excel when you go to EDIT then GO TO can the code be modified with that function. Because that function highlights when you tell it you know its highlights texts or formulas can it be modified into a code to just to find a specific word?

Any Ideas
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top