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:

[TABLE="width: 403"]
<tbody>[TR]
[TD]Group
[/TD]
[TD]Built
[/TD]
[TD] Price
[/TD]
[TD] Fee
[/TD]
[/TR]
[TR]
[TD]Main Group 1 Abcdefg
[/TD]
[TD]426
[/TD]
[TD] $ 903.58
[/TD]
[TD] $ 2.12
[/TD]
[/TR]
[TR]
[TD]Subcategory A
[/TD]
[TD]157
[/TD]
[TD] $ 277.58
[/TD]
[TD] $ 1.77
[/TD]
[/TR]
[TR]
[TD]Subcategory B
[/TD]
[TD]125
[/TD]
[TD] $ 279.06
[/TD]
[TD] $ 2.23
[/TD]
[/TR]
[TR]
[TD]Subcategory C
[/TD]
[TD]144
[/TD]
[TD] $ 346.94
[/TD]
[TD] $ 2.41
[/TD]
[/TR]
[TR]
[TD]Main Group 2 hijklmn
[/TD]
[TD]496
[/TD]
[TD] $ 1,156.45
[/TD]
[TD] $ 2.33
[/TD]
[/TR]
[TR]
[TD]Subcategory D
[/TD]
[TD]75
[/TD]
[TD] $ 170.18
[/TD]
[TD] $ 2.27
[/TD]
[/TR]
[TR]
[TD]Subcategory E
[/TD]
[TD]152
[/TD]
[TD] $ 416.53
[/TD]
[TD] $ 2.74
[/TD]
[/TR]
[TR]
[TD]Subcategory F
[/TD]
[TD]124
[/TD]
[TD] $ 279.67
[/TD]
[TD] $ 2.26
[/TD]
[/TR]
[TR]
[TD]Subcategory H
[/TD]
[TD]145
[/TD]
[TD] $ 290.07
[/TD]
[TD] $ 2.00
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD]922
[/TD]
[TD] $ 2,060.03
[/TD]
[TD] $ 2.23
[/TD]
[/TR]
</tbody>[/TABLE]


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.