0

so far I have this and it's VERY slow for big data sets. Any help 

'For every row in the current selection...
For Counter = 1 To RNG.Rows.Count 'reccnt
    'If the row is an odd number (within the selection)...
    If Counter Mod 2 = 1 Then
        With RNG.Rows(Counter).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent6
                .TintAndShade = 0.799981688894314
                .PatternTintAndShade = 0
        End With
    End If
Next
Johnson Jason
  • 671
  • 1
  • 13
  • 29
  • 1
    Why not use conditional formatting with the folowing formula: `=MOD(ROW(),2)=1`? – Scott Craner Jun 10 '16 at 23:09
  • I am using a VB6 app to work with an excel application object. Can't type in any formulas since I am automating excel. I dump a big array into a sheet and can't automate all of those cells. – Johnson Jason Jun 10 '16 at 23:11
  • is there a way I could select an entire range and apply the formatting you state above to the whole range at once? and how can I select my specific row color? – Johnson Jason Jun 10 '16 at 23:13
  • 2
    You don't have to *type in formulas*. Conditional formatting can be added in code. Record a macro to see what you have to do. – Ken White Jun 10 '16 at 23:17
  • can someone explain the negative vote please? I have code, I am asking how to make it better. What is the issue? – Johnson Jason Jun 10 '16 at 23:39
  • 1
    Yes, you *can* apply formats to multiple ranges at once, and it will run *much* more quickly, but there's a limit to the number of ranges you can union. You can either use the UNION method on a range (to build up the range), or the union operator "," in a range address, like Range("1:1,3:3,5:5") – ThunderFrame Jun 10 '16 at 23:40
  • 2
    You should also disable Application.ScreenUpdating while your code runs, and reset it when you are done. – ThunderFrame Jun 10 '16 at 23:41
  • 1
    see here for some example that you can modify. http://stackoverflow.com/questions/13661965/conditional-formatting-using-excel-vba-code – Scott Craner Jun 10 '16 at 23:43
  • @johnsonjason You **may** have been down voted as you had no question in the body of your question. Yes the subject line is a question but the question itself is not in the body. – Forward Ed Jun 11 '16 at 16:30

3 Answers3

3

Give this a try. I imagine it would speed things up a bit. It runs for me almost instantly.

Sub ColorEven()
    Set rng = Rows("1:40000")
    rng.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0"
    rng.FormatConditions(1).Interior.Pattern = xlSolid
    rng.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
    rng.FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent6
    rng.FormatConditions(1).Interior.TintAndShade = 0.799981688894314
    rng.FormatConditions(1).Interior.PatternTintAndShade = 0
End Sub
Jim Simson
  • 2,774
  • 3
  • 22
  • 30
1

an alternative and very Fast (50k rows in no time) method without conditional formatting:

Option Explicit

Sub main()

    Dim i As Long, nRows As Long
    Dim hlpCol As Range
    Dim indexArray1() As Long, indexArray2() As Long

    With Range("A1:A50000")
        nRows = .Rows.Count '<~~ retrieve n° of rows to be processed
        ReDim indexArray1(1 To nRows) '<~~ redim indexArray1 accordingly
        ReDim indexArray2(1 To nRows) '<~~ redim indexArray2 accordingly

        ' fill indexArrays
        For i = 1 To nRows
            indexArray1(i) = i 'indexArray1, which stores the initial range order
            indexArray2(i) = IIf(.Cells(i, 1).Row Mod 2 = 1, i, nRows + i) 'indexArray2, "marks" range "even" rows to be "after" "uneven" ones
        Next i

        Set hlpCol = .Offset(, .Parent.UsedRange.Columns.Count) '<~~ set a "helper" column ...
        hlpCol.Value = Application.Transpose(indexArray1) '<~~ ... fill it with indexArray1...
        hlpCol.Offset(, 1).Value = Application.Transpose(indexArray2) '<~~ ... and the adjacent one with indexArray2

        .Resize(, hlpCol.Column + 1).Sort key1:=hlpCol.Offset(, 1) '<~~ sort range to group range "uneven" rows before "even" ones

        ' format only half of the range as wanted
        With .Resize(.Rows.Count / 2).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With

        .Resize(, hlpCol.Column + 1).Sort key1:=hlpCol '<~~ sort back the range to its initial order

    End With
    hlpCol.Resize(, 2).Clear '<~~ clear helper columns

End Sub
user3598756
  • 28,893
  • 4
  • 18
  • 28
0

Use a table!! It's automatically colour banded.

Steven Martin
  • 3,150
  • 1
  • 20
  • 27
  • It's most probable that the OP is using VBA for Excel, and there is no color banding for Excel.. – Sreenikethan I Nov 06 '18 at 19:24
  • No sir, Excel tables do **not** have a default banding option. You may try this yourself and confirm. I am saying so, because **there are no tables in Excel! Excel is all about _cells_.** You must be confused with other Office programs like Word and PowerPoint, which do have tables. If you see this link, you can see that creating banded rows actually uses the method used in the Accepted Answer of this Question, just that this link does it by the User Interface, and the Accepted Answer uses VBA. http://www.microknowledge.com/creating-banded-rows-in-excel/ – Sreenikethan I Nov 07 '18 at 17:50
  • 1
    @Sree I think you must be using a version of Excel from 20 years ago,. Excel supports data tables with row banding at the click of a button,. You should do your research before writing a reply – Steven Martin Nov 07 '18 at 19:42
  • Oh well, I didn't do much of a research... Anyway I'm using Excel from Office 365. I never knew the "Insert > Table" option really existed, and I generally create tables manually and use the "Filter" option. TIL something new! I'm sorry for the "confrontation" or whatever that was, please forgive me sir! – Sreenikethan I Nov 08 '18 at 03:53