[VBA] This selection won't format correctly, but others do

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
622
Office Version
  1. 2010
Platform
  1. Windows
Hi all, got two examples for you. The first one (Off Sale) formats correctly, the second one (Unrouted) does not:

VBA Code:
Range("A2:AF" & lastrow).AutoFilter field:=2, Criteria1:="Off Sale"
If Not Range("A2:A" & lastrow).SpecialCells(xlCellTypeVisible).Count > 1 Then
Range("A1").Select
Else
formatrng.SpecialCells(xlCellTypeVisible).Select
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
End With
End If

Range("A2:AF" & lastrow).AutoFilter field:=2, Criteria1:="Unrouted"
If Not Range("A2:A" & lastrow).SpecialCells(xlCellTypeVisible).Count > 1 Then
Range("A1").Select
Else
formatrng.SpecialCells(xlCellTypeVisible).Select
With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
With Selection.Font
    .TintAndShade = -0.349986266670736
End With
End If


What I'm trying to do is filter down to "Unrouted" in column B, which it does, and with all cells selected (which they are), remove any interior cell colours and then colour the font a kind of middle grey so they don't look prominent on the page.

For some reason, this seems to just colour the text as white, even though I got the codes from recording a macro and just taking regular text and colouring it grey. Any reasons why this doesn't work? Thank you.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
Try adjusting it to:

VBA Code:
With Selection.Font
.Color = XlRgbColor.rgbGray
End With
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,137
Office Version
  1. 365
Platform
  1. Windows
As you only have one line inside the With statement, you could just as easily use
VBA Code:
selection.font.color=rgbgrey
Or you can get rid of all the selects like
VBA Code:
With ActiveSheet
   .Range("A2:AF" & 500).AutoFilter field:=2, Criteria1:="Unrouted"
   With .AutoFilter.Range.Offset(1)
      .Font.Color = rgbGrey
      .Interior.Color = xlNone
   End With
End With
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
Beautiful, this worked perfectly, thanks!
Happy to help and thanks for letting me know you have a solution. I would also consider what Fluff is suggesting it may help with future projects. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,645
Messages
5,654,556
Members
418,140
Latest member
ahepple86

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