VBA: Filter rows for text, set filtered rows to Bold font

[JeffHaas]

I have searched quite a bit and can't put the answer to this one together.

I have a spreadsheet similar to this:

Group	Built	Price	Fee
Main Group 1 Abcdefg	426	$ 903.58	$ 2.12
Subcategory A	157	$ 277.58	$ 1.77
Subcategory B	125	$ 279.06	$ 2.23
Subcategory C	144	$ 346.94	$ 2.41
Main Group 2 hijklmn	496	$ 1,156.45	$ 2.33
Subcategory D	75	$ 170.18	$ 2.27
Subcategory E	152	$ 416.53	$ 2.74
Subcategory F	124	$ 279.67	$ 2.26
Subcategory H	145	$ 290.07	$ 2.00
Grand Total	922	$ 2,060.03	$ 2.23

<tbody>
</tbody>

I would like to filter column A for anything with "Main Group" in the name. The names can be much longer and have additional terms in them.

Then I would like to bold all rows which are filtered, so all rows with "Main Group" are bolded.

Then turn off the filter.

Thanks!

 

[My Aswer Is This]

Try this:

Sub Filter_Me_Now()
'Modified 6/21/18 3:45 AM EDT
Dim Lastrow As Long
Dim c As Long
Dim s As Variant
c = "1" ' Column Number Modify this to your need
s = "*Main Group*" 'Saerch Value Modify to your need
Lastrow = Cells(Rows.Count, c).End(xlUp).Row
With ActiveSheet.Cells(1, c).Resize(Lastrow)
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Font.Bold = True
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
End Sub

 

[My Aswer Is This]

My first script Bolded the entire row.
If you only want columns A to D bolded try this script:

Sub Filter_Me_Now()
'Modified 6/21/18 3:55 AM EDT
Dim Lastrow As Long
Dim c As Long
Dim s As Variant
c = "1" ' Column Number Modify this to your need
s = "*Main Group*" 'Saerch Value Modify to your need
Lastrow = Cells(Rows.Count, c).End(xlUp).Row
With ActiveSheet.Cells(1, c).Resize(Lastrow, 4)
    
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Font.Bold = True
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
End Sub

 

[JeffHaas]

Thank you for the quick answer! I tried this out and it works perfectly.

I have a follow-on question: How would you make your subroutine accept a parameter? Right now the search value is always going to be "Main Group".

 

[My Aswer Is This]

Look here in my script and you see I mentioned how to modify search value:

s = "*Main Group*" 'Search Value Modify to your need

so do something like this:
s="Peter"

Or if you will be changing your search value often. I can add a input box into the script which will ask you for the search value
​

 

[JeffHaas]

Sorry if I wasn't clear. I'd like to have your subroutine be more generic. Then you could do this:

Call Filter_Me_Now ("Peter")

And then the subroutine would take the parameter and use that to search for and bold the font. I've Googled it and can't find how to pass a parameter onto an autofilter in VBA.

 

[Fluff]

3 small tweaks should do it

Sub Filter_Me_Now([COLOR=#0000ff]s as string[/COLOR])
'Modified 6/21/18 3:55 AM EDT
Dim Lastrow As Long
Dim c As Long
[COLOR=#ff0000]Dim s As Variant[/COLOR]
c = "1" ' Column Number Modify this to your need
[COLOR=#ff0000]s = "*Main Group*" 'Saerch Value Modify to your need[/COLOR]
Lastrow = Cells(Rows.Count, c).End(xlUp).Row
With ActiveSheet.Cells(1, c).Resize(Lastrow, 4)
    
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Font.Bold = True
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
End Sub

Add the part in blue & remove the parts in red

 

[My Aswer Is This]

3 small tweaks should do it

Sub Filter_Me_Now([COLOR=#0000ff]s as string[/COLOR])
'Modified 6/21/18 3:55 AM EDT
Dim Lastrow As Long
Dim c As Long
[COLOR=#ff0000]Dim s As Variant[/COLOR]
c = "1" ' Column Number Modify this to your need
[COLOR=#ff0000]s = "*Main Group*" 'Saerch Value Modify to your need[/COLOR]
Lastrow = Cells(Rows.Count, c).End(xlUp).Row
With ActiveSheet.Cells(1, c).Resize(Lastrow, 4)
    
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Font.Bold = True
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
End Sub

Add the part in blue & remove the parts in red

I built this script and it works perfectly. I have used it an shared it numerous times as is.

 

[My Aswer Is This]

Sorry if I wasn't clear. I'd like to have your subroutine be more generic. Then you could do this:

Call Filter_Me_Now ("Peter")

And then the subroutine would take the parameter and use that to search for and bold the font. I've Googled it and can't find how to pass a parameter onto an autofilter in VBA.

I really do not know how to answer your question.
You asked a question. I gave you a answer and you said worked perfectly.
Now it sounds like you want a filter to call a Sub Routine.

Maybe someone else can write you a whole new script.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[JeffHaas]

My Aswer - Thank you for taking the time to answer and provide your code! I've only been working with VBA for about six months, and I am still learning how to ask the right questions.

I think Fluff has solved it - he took your code, which works fine for a specific term, and added a feature so your subroutine can be called from another subroutine, with any parameter needed. I will test it out tomorrow.