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

[RockandGrohl]

Hi all, got two examples for you. The first one (Off Sale) formats correctly, the second one (Unrouted) does not:

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.

 

[Trevor G]

Try adjusting it to:

With Selection.Font
.Color = XlRgbColor.rgbGray
End With

 

[RockandGrohl]

Beautiful, this worked perfectly, thanks!

 

[Fluff]

As you only have one line inside the With statement, you could just as easily use

selection.font.color=rgbgrey

Or you can get rid of all the selects like

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]

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.