CODEWITHSUNDEEP
excelvba
Flashheart
Flashheart

Reputation: 103

VBA Cell Format Error

I'm trying to format a group of cells using a for loop (later a conditional while loop)

I'm getting the error '1004'

Method 'Range' of object "Global' failed

I can't figure out how to convert the cells value to a range value that doesn't throw this error. The Cells(x,y) by itself throws an error as well

Thanks in advance

    For row = 2 To 5

    With Range(Cells(row, 5)).Interior
        .Pattern = xlPatternRectangularGradient
        .Gradient.RectangleLeft = 0.5
        .Gradient.RectangleRight = 0.5
        .Gradient.RectangleTop = 0.5
        .Gradient.RectangleBottom = 0.5
        .Gradient.ColorStops.Clear
    End With
    With Range(Cells(row, 5)).Interior.Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Range(Cells(row, 5)).Interior.Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.250984221930601
    End With

Next row

Upvotes: 0

Views: 313

Answers (3)

SierraOscar
SierraOscar

Reputation: 17637

Why are you even looping?

Sub SO()

    With Range(Cells(2, 5), Cells(5, 5)).Interior
        .Pattern = xlPatternRectangularGradient
        .Gradient.RectangleLeft = 0.5
        .Gradient.RectangleRight = 0.5
        .Gradient.RectangleTop = 0.5
        .Gradient.RectangleBottom = 0.5
        .Gradient.ColorStops.Clear
        With .Gradient.ColorStops.Add(0)
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With .Gradient.ColorStops.Add(1)
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = -0.250984221930601
        End With
    End With

End Sub

Upvotes: 1

Steven Martin
Steven Martin

Reputation: 3272

No need for Range()

Just do Cells(row, 5).Interior

  For row = 2 To 5

    With Cells(row, 5).Interior
        .Pattern = xlPatternRectangularGradient
        .Gradient.RectangleLeft = 0.5
        .Gradient.RectangleRight = 0.5
        .Gradient.RectangleTop = 0.5
        .Gradient.RectangleBottom = 0.5
        .Gradient.ColorStops.Clear
    End With
    With Cells(row, 5).Interior.Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Cells(row, 5).Interior.Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.250984221930601
    End With

Next row

Upvotes: 0

Sobigen
Sobigen

Reputation: 2169

Get rid of of Range()

With Cells(row, 5).Interior

You want to either use

Cells(row, col) 
Cells(2, 5)

or

Range(addressString)
Range("E2")

If you don't put a worksheet object in front of either of them they'll refer to the Activesheet or 'Me' if you're working within a worksheet module.

For reference something like:

Dim ws as worksheet
set ws = Thisworkbook.sheets("Sheet1")

ws.cells(2, 5)
ws.Range("E2")

Upvotes: 0

Related Questions