Excel VBA - is it possible to set properties to each cell of a range through the use of a matrix?

Question

I recently found out that it is possible to set values inside each cell of a range with a single command like:

Worksheet.Range(Worksheet.Cells(Row1, Column1), Worksheet.Cells(Row2, Column2)) = MyMatrix

Where MyMatrix is a 2D matrix, with dimensions: Row2-Row1 and Column2-Column1.

Apparently though, if I do the same for applying a property to each cell (let's say .Font.Bold - when MyMatrix is a boolean matrix), it doesn't work:

Worksheet.Range(Worksheet.Cells(Row1, Column1), Worksheet.Cells(Row2, Column2)).Font.Bold = MyMatrix

The command above makes the whole range "bold-flicker" for a fraction of second and then nothing happens. How come?

I would definitely like to avoid the For cycle, because in my code it takes too long.

UPDATE: the same does not work even if I fill MyMatrix with the strings "normal" and "bold" and then write:

Worksheet.Range(Worksheet.Cells(Row1, Column1), Worksheet.Cells(Row2, Column2)).Font.FontStyle = MyMatrix

I also tried (and it doesn't work):

Worksheet.Range(Worksheet.Cells(Row1, Column1), Worksheet.Cells(Row2, Column2)).Cells.Font.FontStyle = MyMatrix

`Font.Bold` doesn't return an array, nor can you set it to one. — Rory, Mar 14 '17 at 15:27
Do two commands, one to set the values and the next to set the format. — Scott Craner, Mar 14 '17 at 15:44
"Font.Bold doesn't return an array". Ok, care to elaborate and maybe answer the question? "Two commands, one to set the values and the next to set the format"... What has that to do with the question? I already have my values set with a command (the one above) and I would like each value to have a bold font depending on another matrix full of "true"s or "false"s. "Using with and .value and .bold"... I already do and the .bold doesn't work, this too has nothing to do with the question. — Noldor130884, Mar 15 '17 at 07:57
Besides, yes, Microsoft says that it is possible to make an entire range bold: https://msdn.microsoft.com/de-de/library/office/ff823025.aspx ; this is not what I want though. — Noldor130884, Mar 15 '17 at 08:02
You're saying Font.Bold = Your entire array, you need to specify which index Font.Bold = MyMatrix(1, 3) — Absinthe, Mar 16 '17 at 09:37
@Absinthe how come then .Value = my entire array works like I want to? — Noldor130884, Mar 16 '17 at 09:50
See http://stackoverflow.com/questions/8451511/vsto-getting-excel-cell-properties-in-bulk — brettdj, Mar 17 '17 at 07:47

score 3 · Answer 1 · answered Mar 17 '17 at 11:07

3

As other answers have said, the .Font property can only be set to a scalar value, not a matrix, but that it can set bulk ranges at once.

One way to get around this would be to construct a String containing the cell references to all the cells that should have a certain font, rather than a matrix of True and False etc. Then just change the font for that range. E.g.

Dim strRange as String
strRange = "A1,B7,C3,D1"    ' set this in a loop or whatever
Worksheet.Range(strRange).Font.Bold = True

answered Mar 17 '17 at 11:07

SteveES

544
5
10

Yes, this is a clever approach (+1), although are there limits to how many individual cells can be used in a range like that? – John Coleman Mar 17 '17 at 11:12
Well, a string has billion of char possible... Right? `Union` on the other hand has a maximum of 30 ranges possible... But since I'd have to loop to build the `strRange` string, I guess I could iterate it... – Noldor130884 Mar 17 '17 at 11:18
2

@Noldor130884 I think that there is a limit of around 8000 non-contiguous cells that can be in a VBA range "Excel only supports a maximum of 8,192 non-contiguous cells through VBA macros" according to this: https://support.microsoft.com/en-us/help/832293/the-.specialcells-xlcelltypeblanks-vba-function-does-not-work-as-expected-in-excel . It is possible that this only applies to earlier versions of Excel. A lot of such limits were increased, often substantially, with Excel 2010 (?) – John Coleman Mar 17 '17 at 11:28
@Noldor130884 Changing the formatting 8,000 cells at a time would have to be a dramatic speed-up. If the idea works out, SteveES probably deserves the bounty (IMHO) – John Coleman Mar 17 '17 at 11:35
17 hours to go X'D – Noldor130884 Mar 17 '17 at 11:38
I must admit, I haven't carried out any speed tests to see whether this approach does actually speed up the process. I would be interested to find out the results if you do decide to try this... – SteveES Mar 17 '17 at 11:43
@SteveES I carried out a sort of proof of concept of a non-optimized version of the idea, and saw noticeable improvement over what I originally did. See my edited answer. – John Coleman Mar 17 '17 at 23:54
This approach will only work if the length of `strRange` is lower than 256. This limit can easily be tested using the test case in my answer. – Axel Richter Mar 18 '17 at 07:29
@AxelRichter are you sure? It appears to be more than that... http://stackoverflow.com/questions/10927764/vba-string-limit – Noldor130884 Mar 20 '17 at 06:56

Axel Richter · Accepted Answer · 2017-03-18T07:27:38.973

According to the documentation of Range.Value Property (Excel) this "Returns or sets a Variant value that represents the value of the specified range". This Variant value can either be one value or an array of values. So

 With ActiveSheet

  .Range("A1:B3").Value = [{1,2;3,4;5,6}]  

  aValues = .Range("A1:B3").Value

 End With

will work.

But Range.Font Property (Excel) "Returns a Font object that represents the font of the specified object.". That means one Font object and not an array of Font objects. So

...
  aFonts = .Range("A1:B3").Font
...

will not work. Neither

...
  .Range("A1:B3").Font = aFonts
...

will work.

One could do

...
  Set oFont = .Range("A1:B3").Font
...

but that oFont will also be one Font object for the whole range.

So

...
  oFont.FontStyle = "bold italic"
...

or

...
  oFont.Bold = True
...

will always affect the whole range.

Solutions:

The best idea would really be the one of @SteveES. It is using a range which is a union of all cells which shall be bold. But this approach will only work if the length of strRange is lower than 256. This limit can easily be tested using the following:

Dim strRange As String
For r = 1 To 125 Step 2
 strRange = strRange & "A" & r & ","
Next
strRange = Left(strRange, Len(strRange) - 1)
MsgBox Len(strRange)
With ActiveSheet
 .Range(strRange).Font.Bold = True
End With

This will fail at .Range(strRange).Font.Bold = True because Len(strRange) is 259 . If the loop of r is from 1 to 124 only, then it will work having Len(strRange) = 254.

So if the requirement is having a random number of cells which shall be formatted bold and cannot be determinated using conditional formatting, the most performant solution for me is really a loop over all cells having Application.ScreenUpdating = False while looping and setting bold.

Sub setRangeValuesWithStyles()

 lRows = 100
 lCells = 100

 ReDim aValues(1 To lRows, 1 To lCells) As Variant
 ReDim aFontBolds(1 To lRows, 1 To lCells) As Boolean

 For r = 1 To lRows
  For c = 1 To lCells
   Randomize
   iRnd = Int((100 * Rnd()) + 1)
   aValues(r, c) = IIf(iRnd < 50, "T" & iRnd, iRnd)
   Randomize
   iRnd = Int((100 * Rnd()) + 1)
   aFontBolds(r, c) = IIf(iRnd < 50, True, False)
  Next
 Next

 lStartRow = 5
 lStartCol = 5

 With ActiveSheet
  Set oRange = .Range(.Cells(lStartRow, lStartCol), .Cells(lStartRow + lRows - 1, lStartCol + lCells - 1))
  oRange.Value = aValues
  Application.ScreenUpdating = False
  For r = 1 To lRows
   For c = 1 To lCells
    oRange.Cells(r, c).Font.Bold = aFontBolds(r, c)
   Next
  Next
  Application.ScreenUpdating = True
 End With

End Sub

Even using Union for partially ranges (cells in each rows for example) the performance is not better but more worse in my tests.

Before flagging as answered, is there ANY way I can set font properties without having to use `For` cycles? Is it possible that no one had already solved this peculiar problem before? It takes ages to set those properties... — Noldor130884, Mar 17 '17 at 07:43
"set font properties without having to use `For` cycles" To what exactly to achieve? Maybe conditional formatting would be the better approach? — Axel Richter, Mar 17 '17 at 08:11
If I had the possibility to do that, I'd use the conditional formatting, but in my program, there are a bunch of conditions that ultimately set if the number to put in a certain cell has to be formatted in bold or not, depending on how the number is calculated, not on its value. "what exactly to achieve" is exactly what I asked: I need to speed up my program, and I wanted to know if I could avoid going through FOR cycles. — Noldor130884, Mar 17 '17 at 08:36
Is it not possible for a string to be > 256 chars? http://stackoverflow.com/questions/10927764/vba-string-limit — Noldor130884, Mar 20 '17 at 06:58
This is not a String limitation but seems to be a limitation for address strings in `Range(addressString)`. It seems not documented but it can easily be shown using my example. — Axel Richter, Mar 20 '17 at 07:12

John Coleman · Answer 3 · 2017-03-17T23:52:48.457

As others have pointed out, this isn't possible, at least in any direct way.

If you do this sort of thing a lot, you could abstract it to a sub, one which:

Turns off screen-updating and automatic calculation Calculates the
default setting of Bold -- the majority in the Boolean matrix
Sets the whole range to the default
Loops through the cells, changing no more than half the cells
Restores screen-updating and calculation mode to what they were when the sub was called

Sub BoldFace(MyRange As Range, MyMatrix As Variant)
    'The dimensions of MyRange and MyMatrix are assumed the same
    'no error checking

    Dim i As Long, j As Long, m As Long, n As Long
    Dim su As Boolean, ac As Long
    Dim default As Boolean
    Dim TrueCount As Long

    su = Application.ScreenUpdating
    Application.ScreenUpdating = False
    ac = Application.Calculation
    Application.Calculation = xlCalculationManual

    m = MyRange.Rows.Count
    n = MyRange.Columns.Count
    For i = 1 To m
        For j = 1 To n
            If MyMatrix(i, j) Then TrueCount = TrueCount + 1
        Next j
    Next i

    default = TrueCount > m * n / 2 'defaults to true if over half the matrix is true
    MyRange.Font.Bold = default

    With MyRange
        For i = 1 To m
            For j = 1 To n
                If MyMatrix(i, j) = Not default Then .Cells(i, j).Font.Bold = MyMatrix(i, j)
            Next j
        Next i
    End With

    Application.ScreenUpdating = su
    Application.Calculation = ac
End Sub

Tested like:

Sub test()
    Dim i As Long, j As Long
    Dim R As Range, m As Variant
    Dim start As Double, elapsed As Double

    Randomize

    ReDim m(1 To 10000, 1 To 100)
    For i = 1 To 10000
        For j = 1 To 100
            m(i, j) = Rnd() < 0.9
        Next j
    Next i

    Set R = Range(Cells(1, 1), Cells(10000, 100)) '1 million cells!
    start = Timer
    BoldFace R, m
    elapsed = Timer - start
    Debug.Print elapsed

End Sub

When I run it this way, where 500,000 cells (on average) need to be changes, it takes about 15.3 seconds on my machine. If I change the line m(i, j) = Rnd() < 0.5 to m(i, j) = Rnd() < 0.1 (so only 10% of the cells will need to be changed) it takes about 3.3 seconds.

On Edit I was curious to see how the idea of @SteveES would pan out. The following is a non-aggressive approach that does it row by row, and is meant more as proof of concept. A more aggressive approach would wait until Union throws an error and then discharge then:

Sub BoldFace(MyRange As Range, MyMatrix As Variant)
    'The dimensions of MyRange and MyMatrix are assumed the same
    'no error checking

    Dim i As Long, j As Long, k As Long, m As Long, n As Long
    Dim lim As Long, needsFixed As String, toFix As Range
    Dim su As Boolean, ac As Long
    Dim default As Boolean
    Dim TrueCount As Long

    su = Application.ScreenUpdating
    Application.ScreenUpdating = False
    ac = Application.Calculation
    Application.Calculation = xlCalculationManual

    m = MyRange.Rows.Count
    n = MyRange.Columns.Count

    For i = 1 To m
        For j = 1 To n
            If MyMatrix(i, j) Then TrueCount = TrueCount + 1
        Next j
    Next i

    default = TrueCount > m * n / 2 'defaults to true if over half the matrix is true
    MyRange.Font.Bold = default

    With MyRange
        For i = 1 To m
            k = 0
            Set toFix = Nothing
            For j = 1 To n
                If MyMatrix(i, j) = Not default Then
                    k = k + 1
                    If toFix Is Nothing Then
                        Set toFix = .Cells(i, j)
                    Else
                        Set toFix = Union(toFix, .Cells(i, j))
                    End If
                End If
            Next j
            toFix.Font.Bold = Not default  
        Next i
    End With

    Application.ScreenUpdating = su
    Application.Calculation = ac
End Sub

In any event, when I run this code with exactly the same test sub as above then it takes around 7 seconds (rather than 15) on my machine. If the savings are 50% by only accumulating 50-100 cells before fixing the font, it would probably be even better for even more aggressive approaches.

The number of the cells are really random, and I cannot predict if they are 10% or 60% or whatever. I do all the first steps you suggested already, but you'd be amazed about how my colleagues pressure me to speed everything up anyway. Thanks for pointing out that the code MAY NOT be sped up more than it is... — Noldor130884, Mar 17 '17 at 11:14
@Noldor130884 The default approach as opposed to a more naïve looping (which I did at first) got it from around 30 seconds to 15 seconds, so pre-calculating should give about a 50% speedup in the worst case, at least for large ranges. For smaller ranges, the looping through the matrix twice probably isn't worth it. — John Coleman, Mar 17 '17 at 11:30

score 2 · Answer 4 · answered Mar 17 '17 at 20:55

2

You could use your matrix in a FormatCondition to apply the formatting.

This example formats each cell in range Sheet1!A1:B10 if the opposing cell in the matrix range Sheet2!A1:B10 is True:

' update the matrix
Range("Sheet2!A1:B10").Value2 = MyMatrix

' add a format condition
With Range("Sheet1!A1:B10").FormatConditions.Add(xlExpression, , "=Sheet2!A1:B10=True")
    .Font.Bold = True
    .Interior.Color = 255
End With

answered Mar 17 '17 at 20:55

Florent B.

41,537
7
86
101

Even if this is a very good solution (+1), I would like to avoid this... I'm afraid it creates a conditional formatting that may bother my colleagues later... – Noldor130884 Mar 20 '17 at 07:00

EEM · Answer 5 · 2017-03-18T06:52:11.067

Try this function:

Rng_fBooleanProperties_ByArray(exRngProp, rTrg, aProperty)

User defined function that sets the following Boolean Range Properties: AddIndent, Font.Bold, Font.Italic, Font.Strikethrough, Font.Subscript, Font.Superscript, FormulaHidden, Locked, ShrinkToFit, UseStandardHeight, UseStandardWidth and WrapText. Returns True if successful.

Syntax

exRngPropAs E_RngProp: Customized Enumeration to define the range property to be updated.

rTrgs Range: Target range to updated.

aPropertyAs Variant: Array of booleans with cells to be updated.

It uses:

• An Array to hold the Target Range actual contents (i.e. Numbers, Text, Logical, Error, Formulas).

• The E_RngProp Enumeration to define and identify the property to be updated.

• The Range.Value property to enter the Boolean Array into the Target Range.

• The Range.Replace method to change the False values into empty cells.

• The Range.SpecialCell method to set the corresponding Range.Property as required using each Cell.Value.

This is the code:

Option Explicit

Enum E_RngProp
    Rem Range Properties - Boolean & Read\Write
    exAddIndent = 1
    exFontBold
    exFontItalic
    exFontStrikethrough
    exFontSubscript
    exFontSuperscript
    exFormulaHidden
    exLocked
    exShrinkToFit
    exUseStandardHeight
    exUseStandardWidth
    exWrapText
    End Enum

Function Rng_fBooleanProperties_ByArray(exRngProp As E_RngProp, rTrg As Range, aProperty As Variant) As Boolean
Dim rPropOn As Range
Dim aFml As Variant

    Rem Validate Input
    If rTrg Is Nothing Then Exit Function
    If Not IsArray(aProperty) Then Exit Function
    If rTrg.Rows.Count <> UBound(aProperty) Then Exit Function
    If rTrg.Columns.Count <> UBound(aProperty, 2) Then Exit Function

    With rTrg

        Rem Get Formulas from Target Range
        aFml = .Formula

        Rem Apply Bold Array to Target Range
        .Value = aProperty
        .Replace What:=False, Replacement:="", _
            LookAt:=xlWhole, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        On Error Resume Next
        Set rPropOn = .SpecialCells(xlCellTypeConstants, 23)
        On Error GoTo 0

        Select Case exRngProp
        Case exAddIndent
            .AddIndent = False
            If Not rPropOn Is Nothing Then rPropOn.AddIndent = True

        Case exFontBold
            .Font.Bold = False
            If Not rPropOn Is Nothing Then rPropOn.Font.Bold = True

        Case exFontItalic
            .Font.Italic = False
            If Not rPropOn Is Nothing Then rPropOn.Font.Italic = True

        Case exFontStrikethrough
            .Font.Strikethrough = False
            If Not rPropOn Is Nothing Then rPropOn.Font.Strikethrough = True

        Case exFontSubscript
            .Font.Subscript = False
            If Not rPropOn Is Nothing Then rPropOn.Font.Subscript = True

        Case exFontSuperscript
            .Font.Superscript = False
            If Not rPropOn Is Nothing Then rPropOn.Font.Superscript = True

        Case exFormulaHidden
            .FormulaHidden = False
            If Not rPropOn Is Nothing Then rPropOn.FormulaHidden = True

        Case exLocked
            .Locked = False
            If Not rPropOn Is Nothing Then rPropOn.Locked = True

        Case exShrinkToFit
            .Locked = False
            If Not rPropOn Is Nothing Then rPropOn.ShrinkToFit = True

        Case exUseStandardHeight
            .UseStandardHeight = False
            If Not rPropOn Is Nothing Then rPropOn.UseStandardHeight = True

        Case exUseStandardWidth
            .UseStandardWidth = False
            If Not rPropOn Is Nothing Then rPropOn.UseStandardWidth = True

        Case exWrapText
            .WrapText = False
            If Not rPropOn Is Nothing Then rPropOn.WrapText = True

        End Select

        Rem Reset Formulas in Target Range
        .Formula = aFml

    End With

    Rem Set Results
    Rng_fBooleanProperties_ByArray = True

End Function

Additionally having these lines at the beginning of your main procedure will help to speed up the process:

With Application
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

And these lines at the end of your main procedure:

With Application
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
End With

The function can be called using any of these methods:

If Not (Rng_fBooleanProperties_ByArray(exFontBold, rTrg, aBold)) Then GoTo Error_Message

OR

Call Rng_fBooleanProperties_ByArray(exFontItalic, rTrg, aItalic)

OR

Rng_fBooleanProperties_ByArray exFontStrikethrough, rTrg, aStrikethrough

Suggest to read the following pages to gain a deeper understanding of the resources used:

Enum Statement, Function Statement, On Error Statement,

Range Object (Excel), Range.Replace Method (Excel), Range.SpecialCells Method (Excel),

Select Case Statement, Using Arrays, With Statement.

Well it seems that you just did not try the function. If so you’ll learn that there is no need to For...Next this function through the cells, just create the boolean array as you are doing it now and send it to the function, the function will take care of it without using For...Next. It process the array as a whole. Try it then come back with your comments. — EEM, Mar 20 '17 at 12:33
Yeah, sorry about that, I misunderstood your code...and you are right. This is a little bit more complete than the answer provided by @Michael . As I just wrote on a comment after his answer, I did test this and eventually it came up that the solution is fast enough. The best one remains the one I gave the bounty too, even if it has the "256 chars" flaw. Finally, I ended up using a simplified version of the code of Michael, which I split and embedded into my code. — Noldor130884, Mar 21 '17 at 06:48

Michael · Answer 6 · 2017-03-18T16:52:35.027

You could use a temporary dummy worksheet and Paste Special for a solution that doesn't require any looping or persistent data changes, can apply multiple fonts at once, can incorporate additional formatting changes and has larger limits on size (restricted only by the number of cells in named ranges and that which Replace can operate on).

Start by first creating/saving/pasting your matrix of boolean values to a new dummy worksheet/range (or text descriptors to handle multiple formats at once):

Then, use the Replace method once for each font style you have in your matrix, replacing the text with the same text but replacing the format with the corresponding style. You then have a range with the formatting that you want to apply to your actual data:

Then, you just copy the format range and use PasteSpecial to paste only the Formats onto your data range. Lastly you can delete the dummy sheet/range if it's no longer useful.

This can all be done in VBA fairly quite simply. The follow sub is a complete solution if the data to be formatted is in named range "Data" and the matrix of formats has been constructed in named range "Fonts" (still just as plain text and using the values as per the first image above, which can be done by saving your MyMatrix to a new sheet and naming the range).

Sub CopyFonts()

    With Range("Fonts")
        Application.ReplaceFormat.Font.FontStyle = "Bold"
        .Replace What:="bold", Replacement:="bold", SearchFormat:=False, ReplaceFormat:=True
        Application.ReplaceFormat.Font.FontStyle = "Italic"
        .Replace What:="italics", Replacement:="italics", SearchFormat:=False, ReplaceFormat:=True
        .Copy
    End With

    Range("Data").PasteSpecial Paste:=xlPasteFormats

    Application.CutCopyMode = False

End Sub

I've also done some performance testing for comparison. I repeated the above pattern over 1 million cells A1:J100000. From plain text in the fonts range, it took 16 seconds in total to apply the two replaces and paste the formatting to the data range (with Screenupdating set to false).

If bold is the only FontStyle that you want and your matrix just has values of TRUE and FALSE, then just keep the 2 lines of code that apply bold formatting, searching on the value of "TRUE" instead of "bold". Alternatively, additional or more complex formats can be easily specified in the replacement formats.

This is a no-go :( Creating a worksheet to do that (even if it IS a nice workaround), would mean a lot more time and memory. At least it's what I can see on my computer — Noldor130884, Mar 20 '17 at 06:51
If you already have the matrix of Boolean values constructed in an array variable in your code (which it sounds like you're already doing), then saving those values to a new blank worksheet (by the same manner as the first snippet of code in your question) shouldn't add much time at all. I just did a test creating a Boolean array with 1 million values in VBA (100000 by 10 as per my previous test); and just saving those plain values to a blank worksheet only took 6 seconds. Also remember, as soon as you've applied the formats, you can delete the temporary worksheet of formatted Boolean values. — Michael, Mar 20 '17 at 07:51
I'm a little surprised, but if you're really struggling for memory during processing, then immediately after saving the values to the worksheet you could try redimensioning the array variable to 1 by 1 to free up its memory. (I'm not actually sure how much impact this would have though, if any.). Can you please clarify what's actually taking a long time with this approach? — Michael, Mar 20 '17 at 07:52
I basically have multiple different sheets with a lot of data in it. Let's suppose I have only added 1 single worksheet, and stored there the format. This would mean: wipe any format there was, copy true or false values, replace values by formatting, copy format, paste format to another page... This is a bit "heavy" in terms of performance — Noldor130884, Mar 20 '17 at 07:54
From my testing it seemed that a single application to a million cells had pretty much the same performance as the other best solution so far. Any solution will have to be repeated for each sheet and Range("Formats").Clear between applications is virtually instantaneous. Copying and pasting formatting does has a performance overhead, but it is offset by the fact that you don't require any looping in your code, which is what you're trying to avoid but is required in all other solutions. Have you actually tried this solution on your data? — Michael, Mar 20 '17 at 09:25
I had some minutes to test your code, even though for my application, I've had to mold it to my needs, and insert different parts of it, in my code. In this case you're right. It does have a good performance. It gains some seconds on something like 10.000 cells splitted along different worksheets. — Noldor130884, Mar 21 '17 at 06:43

S Meaden · Answer 7 · 2017-03-17T22:04:57.523

It is not possible. However, you have set a bounty and spent some points so I can give some related tips. So to save code you could arrange your formats into VBA Styles.

So you create a style once and then it is a one-liner to set a range. That should save some time. Here is some sample code.

Option Explicit

Sub TestSetUpStyle()

    Dim stylFoo As Excel.Style
    On Error Resume Next
    Set stylFoo = ThisWorkbook.Styles.Item("foo")
    stylFoo.Delete
    Set stylFoo = Nothing
    On Error GoTo 0

    If stylFoo Is Nothing Then
        'https://msdn.microsoft.com/en-us/library/office/ff821826.aspx
        Set stylFoo = ThisWorkbook.Styles.Add("foo")

        '* I CAN SET ALL SORTS OF STYLE PROPERTIES ONCE HERE ...
        stylFoo.Font.Name = "Arial"
        stylFoo.Font.Size = 18

        stylFoo.Interior.ColorIndex = 3
        With stylFoo.Borders
            .LineStyle = xlContinuous
            .Color = vbRed
            .Weight = xlThin
        End With

        stylFoo.NumberFormat = "$000.00"

    End If

    Sheet1.UsedRange.Style = "foo" '* THEN IN ONE LINE WE SET ALL THOSE PROPERTIES

End Sub

Also for speed set Application.ScreenUpdating = False for the duration of the sheet writing/formatting. You could use a Class to help manage this using a RAII pattern.

This is a damn good thing to learn, but sadly it doesn't solve my problem. The only thing I have to do is to set fonts as bold, and this doesn't address the issue of making a whole range of cells bold or normal depentently from a boolean matrix — Noldor130884, Mar 20 '17 at 07:04

Excel VBA - is it possible to set properties to each cell of a range through the use of a matrix?

7 Answers7