[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 590
  • Last Modified:

Cells - Replaciing Border Colors

I have a worksheet and want to make a formatting change to a selected range.  At the moment there are three row areas that are being selected, with multiple columns.

Example:
A Range is selected, at the moment about three rows and X (from 1 to 7, it depends) columns.
The first selected row, I would want the format to be:
Fill:
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 14540253
With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .Color = 6304256

For the Next two rows the following needs to occur:
Range("C10:E10").Select  '(it could be ("C10:I10")
    With Selection.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 90
        .Gradient.ColorStops.Clear
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0)
        .Color = 15131874
        .TintAndShade = 0
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0.5)
        .Color = 16512499
        .TintAndShade = 0
    End With
    With Selection.Interior.Gradient.ColorStops.Add(1)
        .Color = 15131874
        .TintAndShade = 0
    End With
Range (1st column and all other rows -- at the moment its only two, or rows 2&3 of selection), this example it's "C10"
   With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Color = 6304256
 End With
' The second column, rest of rows, or rows 2 and 3)
    Range("D10").Select          'Col D happens to be the 2nd column in the selection
    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .Color = 9521920
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
End Sub

Open in new window


The selection will be variable and the columns and / or rows won't be fixed.  How can I make this variable so that, in reality, the first row is Gray with a Dark Blue-Bold font, and the rest of the rows are gradient, as shown, with the first column font being a dark blue regular, and the second column font being a different and bolded blue.

I have some code that will work if everything is of the same type, but not one where there are different combinations of formatting.
0
Cook09
Asked:
Cook09
  • 3
  • 3
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

why don't you create a new Table Style  and apply that to the selection?

cheers, teylyn
0
 
Cook09Author Commented:
I'm not quite sure how to do that.

I've attached the style that I would like achieve though.  This is only piece of a demonstration table if that makes any difference.
Style.xlsx
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
The file you posted has a gazillion cell styles in the styles panel. You may want to delete these.

You can create a table style by selecting an existing table, then clicking the table tools ribbon > Table Styles drop-down > New Table Style.

Table styles include styles for header row, footer row, first column, last column, banded rows, banded columns, etc. For each of these elements you can set a style.

Be aware that some of the styles only show when the table is set to use them. On the table tools ribbon, if you untick "first column", for example, then no special formatting will be applied to the first column.

See attached file.

cheers, teylyn
27827338-table-styles.xlsx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Cook09Author Commented:
Thanks, your example made it very easy to understand. Appreciate your help.
0
 
Cook09Author Commented:
teyln,
One quick question. When I apply the style the filter buttons automatically appear.  Is that normal, or is there a way to apply the style without the filter automatically being activated?

Ron
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Since it's a table style, the filter buttons automatically appear when you apply the table style. You can revert the table back to a range with the command on the Table tools ribbon.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now