VBA, how to reference several cells in one line [ tidy code ]

sameerbuch · Mar 25th 2013

How can i make this tidier, the part where i change the colour of the cells towards the bottom, i am just repeating code, can I just select the desired ranges in one line?

Code

'clear contents of the unwanted subtotals
Dim Rng As Range, List As Object, Rw As Long
Set List = CreateObject("Scripting.Dictionary")
With Sheets("BC")
  For Each Rng In .Range("B3", .Range("B" & Rows.Count).End(xlUp))
    If Not List.Exists(Rng.Value) Then
      List.Add Rng.Value, Nothing
    End If
  Next
End With
With Sheets("Summary")
  For Rw = .Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    If List.Exists(.Cells(Rw, "A").Value) Then
    .Cells(Rw, "B").ClearContents
    .Cells(Rw, "C").ClearContents
    .Cells(Rw, "D").ClearContents
    
'change format of headers to grey, change header fonts to bold
    .Cells(Rw, "C").Select
   
        With selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorDark1
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = -0.149998474074526
    End With
     .Cells(Rw, "D").Select
   
        With selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorDark1
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = -0.149998474074526
        
        .Cells(Rw, "E").Select
   
        With selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorDark1
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = -0.149998474074526
    End With

Display More

dangle · Mar 25th 2013

Re: VBA, how to reference several cells in one line [ tidy code ]

Yes you can tidy this up a fair bit. Try to avoid using 'select' on ranges. It is unncessary and slows down the code. It is better to refer to the range directly or use a 'with' statement.

Try this:

Code

If List.Exists(.Cells(Rw, "A").Value) Then
            .Cells(Rw, 2).Resize(, 3).ClearContents
            With .Cells(Rw, 3).Resize(, 3).Interior
                .Pattern = xlSolid
                .PatternThemeColor = xlThemeColorDark1
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.149998474074526
                .PatternTintAndShade = -0.149998474074526
            End With
        End If

VBA, how to reference several cells in one line [ tidy code ]

Participate now!

Share