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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
697
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,708
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
75,967
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,708
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)
 

Forum statistics

Threads
1,171,185
Messages
5,874,222
Members
433,037
Latest member
Not Moose

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