CODEWITHSUNDEEP
vbaexcel
PVD
PVD

Reputation: 37

Store cell format/style as variable in VBA

I am trying to work out an easy way to format output sheets created in VBA. In this connection, it would be convenient to be able to store a “complete” cell format/style in a variable or function that can be applied at different points throughout my code.

As an example, in my current project I want to apply the following formatting on several cells:

With CellX
    .ClearFormats
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeRight).Color = RGB(217, 217, 217)
    .Interior.Color = RGB(127, 126, 130)
    .Font.name = "Arial"
    .Font.Size = 8
    .Font.Color = RGB(255, 255, 255)
    .HorizontalAlignment = xlRight
    .Font.Bold = True
End With

Due to the structure of my code, I can’t (/don’t want to) create a loop where I apply the above formatting to all the cells I want formatted that way. Rather, it would be convenient if I could define the above as a variable or something and e.g. apply it within an if statement through a single line.

If [Variable] = True Then
[Apply formatting to] CellX
End if

Anyone have any suggestions on how to do this in a good way?

Upvotes: 2

Views: 2052

Answers (1)

Radek Piekný
Radek Piekný

Reputation: 145

how about function. Create a function with parametr as range and then just use it.

Actually it should be sub, something like this:

Sub formatRange(CellX As Range)
    With CellX
        .ClearFormats
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeRight).Color = RGB(217, 217, 217)
        .Interior.Color = RGB(127, 126, 130)
        .Font.Name = "Arial"
        .Font.Size = 8
        .Font.Color = RGB(255, 255, 255)
        .HorizontalAlignment = xlRight
        .Font.Bold = True
    End With
End Sub
Sub bla()
    If [Variable] = True Then
        formatRange (CellX)
    End If
End Sub

Upvotes: 5

Related Questions