Highlights Cells Begins with Multiple Criteria

carlbbq · Oct 4th 2015

VBA noob here...

What would be the easiest and cleanest way to perform the following please?

1) User inputs desired criteria in Column B, and clicks the button to perform find&highlight function (can be very long criteria listed)
2) Macro collects all criteria, finds all cells in the list (Colmn A) that begins with any criteria from column B, and highlights the cells in Column A in red.

My ideas would be to get B1 into 'criteria' as string, determines the number of chars in the 'criteria', and use LEFT function to locate any cells matches (begins with) in column A and highlights in red.
And then get B2, B3, B4...until end of the Column B.

However, this would require using a FOR loop, which could be slow if the list is 10000+ rows.
Also, LEFT function would require number of chars, which I'll have to determine for every criteria.

Is there any faster and easier way?

An example of sample data and result are below.

Raw data:
[TABLE="class: grid, width: 500, align: left"]

[tr]

[td]

List (Col A)

[/td]

[td]

Criteria (Col B, User input examples)

[/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]absinths

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td]

absi

[/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]absent

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td]

ele

[/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]asenath

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td]

Com

[/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]Conformate

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td]

Confla

[/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]aseneth

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]conflict of interest

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]absinthes

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]confluent

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]abesant

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]absinthe's

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]Compete

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]absynth

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]abs

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]confiscable

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]conflate

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]eat

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]Electricity

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]confiscate

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]conflation

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 170"]

[tr]

[TD="width: 170"]conflagration

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td][/td]

[/tr]

[/TABLE]

Result:
[TABLE="class: grid, width: 500, align: left"]

[tr]

[td]

List (Col A)

[/td]

[/tr]

[tr]

[td]

absinths

[/td]

[/tr]

[tr]

[td]

absent

[/td]

[/tr]

[tr]

[td]

asenath

[/td]

[/tr]

[tr]

[td]

Conformate

[/td]

[/tr]

[tr]

[td]

aseneth

[/td]

[/tr]

[tr]

[td]

conflict of interest

[/td]

[/tr]

[tr]

[td]

absinthes

[/td]

[/tr]

[tr]

[td]

confluent

[/td]

[/tr]

[tr]

[td]

abesant

[/td]

[/tr]

[tr]

[td]

absinthe's

[/td]

[/tr]

[tr]

[td]

Compete

[/td]

[/tr]

[tr]

[td]

absynth

[/td]

[/tr]

[tr]

[td]

abs

[/td]

[/tr]

[tr]

[td]

confiscable

[/td]

[/tr]

[tr]

[td]

conflict

[/td]

[/tr]

[tr]

[td]

eat

[/td]

[/tr]

[tr]

[td]

Electricity

[/td]

[/tr]

[tr]

[td]

confiscate

[/td]

[/tr]

[tr]

[td]

conflation

[/td]

[/tr]

[tr]

[td]

conflagration

[/td]

[/tr]

[/TABLE]

**pike** · Oct 4th 2015

Re: Highlights Cells Begins with Multiple Criteria

Hi carlbbq
Welcome to the forum
Can you adapt the code below which will highlight the search text red

Code

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


    For Each ws In Worksheets
        With Sheets(ws.Name)
            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

Display More

carlbbq · Oct 4th 2015

Re: Highlights Cells Begins with Multiple Criteria

Hi Pike,

wow Array?
I've never used it before, I will try it out and let you know. Seems clean enough!

Thanks for the prompt reply!

carlbbq · Oct 8th 2015

Re: Highlights Cells Begins with Multiple Criteria

Hi Pike,

The function ".Cells.Find" only finds either partial or exact value from specified range correct?
What I need is actually find and highlight cells that begins with specified criteria. Can .cells.find do that?

Thanks!

jindon · Oct 8th 2015

Re: Highlights Cells Begins with Multiple Criteria

Just a little alteration of pike's

Code

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 = Range("b2", Range("b" & Rows.Count).End(xlUp)).Value




    For Each ws In Worksheets
        With Sheets(ws.Name)
            For Each xItem In Fnd
                Set Lookin = Columns(1).Find(xItem & "*", , , 1, , , 0)
                If Not Lookin Is Nothing Then
                    ff = Lookin.Address
                    Do
                        Lookin.Characters(1, Len(xItem)).Font.ColorIndex = 3
                        Set Lookin = Columns(1).FindNext(Lookin)
                    Loop Until ff = Lookin.Address
                End If
                Set Lookin = Nothing
            Next
        End With
    Next
     
End Sub

Display More

carlbbq · Oct 8th 2015

Re: Highlights Cells Begins with Multiple Criteria

Hi Jindon,
Thanks for the reply!

Can you help to explain what this line does please? I just tried your code but it doesn't highlight any cell now

Set Lookin = Columns(1).Find(xItem & "*", , , 1, , , 0)

jindon · Oct 8th 2015

Re: Highlights Cells Begins with Multiple Criteria

That is equivalent to

Code

Set Lookin = Columns(1).Find(What:= xItem & "*", LookAt:= xlWhole, MatchCase:= False)

xItem & "*" means cell begin with xItem.

Code

Sub HighlightCells()
    Dim Lookin As Range, ff As String
    Dim xItem As Variant, FND
     
    FND = Range("b2", Range("b" & Rows.Count).End(xlUp)).Value




    For Each xItem In FND
        Set Lookin = Columns(1).Find(xItem & "*", , , 1, , , 0)
        If Not Lookin Is Nothing Then
            ff = Lookin.Address
            Do
                Lookin.Characters(1, Len(xItem)).Font.ColorIndex = 3
                Set Lookin = Columns(1).FindNext(Lookin)
            Loop Until ff = Lookin.Address
        End If
        Set Lookin = Nothing
    Next
End Sub

Display More

carlbbq · Oct 8th 2015

Re: Highlights Cells Begins with Multiple Criteria

Hi Jindon,

This works perfectly!! Thank you very very much!!

Highlights Cells Begins with Multiple Criteria

Participate now!

Share