CODEWITHSUNDEEP
vbarangeruntime-error
user2539552
user2539552

Reputation: 51

VBA runtime error 1004

I'm a little new to VBA and am getting a runtime error that I can't solve. I have the following code 

Dim vRange As Range
Sheets("Vert E").Cells.FormatConditions.Delete
With Sheets("Vert E")
    Set vRange = .Range(.Cells(2, 2))
End With

vRange.Select

The last line, vRange.Select is throwing an error that says Run-time error '1004': Application-defined or object-defined error. Any ideas?

Upvotes: 1

Views: 7481

Answers (2)

user2140173
user2140173

Reputation:

Shorter and neater way of doing it

 Dim vRange As Range
 Dim ws as Worksheet
 Set ws = Sheets("Vert E")
 ws.Cells.FormatConditions.Delete
 Set vRange = ws.Cells(2, 2)
 ws.Activate
 vRange.Select

note: avoid using Select

Update:

To apply some formatting to a cell without Selecting it use

below example changes the color of the cell

vRange.Interior.Color = RGB(200, 100, 50)

In VBE just type vRange.Interior. and make use of the VBE Intellisense which should help you a bit by listing the available properties of the Interior class.

The best way to apply formatting to a cell is to do it while recording a macro and then editing the code in the VBE.

Upvotes: 1

Cor_Blimey
Cor_Blimey

Reputation: 3310

A couple of things:

1) Remove .Range(_) around .Cells(2,2): it is unnecessary

2) Move the With further up

3) Range.Select can only be used if the Range's worksheet is the active sheet. So to confirm it, add a like .Activate above vRange.Select, and move the End With to below vRange.Select.

Like:

Dim vRange As Range
With Sheets("Vert E")
    .Cells.FormatConditions.Delete
    Set vRange = .Cells(2, 2)
    .Activate
    vRange.Select
End With

Hope that helps

Upvotes: 1

Related Questions