VBA code to highlight a specific text in cell

[vane0326]

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!

 

[Legacy 98055]

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]

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?

 

[Legacy 98055]

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]

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]

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]

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]

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

 

[Legacy 98055]

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".

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]

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