1

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?

user2539552
  • 51
  • 2
  • 5

2 Answers2

1

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

Cor_Blimey
  • 3,260
  • 1
  • 14
  • 20
1

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.

Community
  • 1
  • 1
  • And, follow up question, what I'm actually trying to do is apply a conditional formatting rule to a range of cells. The extents of the range get calculated by a function I have. You mentioned to avoid using select. Is there another way to apply conditional formatting? – user2539552 Aug 12 '13 at 14:46
  • @user2539552 you need to `Activate` the sheet in order to be able to use `Select` method of a [Range Object](http://msdn.microsoft.com/en-us/library/office/ff838238.aspx). I can't give a better explanation as it's just how [`Select`](http://msdn.microsoft.com/en-us/library/office/ff197597.aspx) method works and even MSDN does not provide a better explanation. So in easy words, you always have to make sure you are working on an active sheet when using `Select` method. See updated answer –  Aug 12 '13 at 14:49
  • 1
    @user2539552 but as mehow says, you really only need to use Select when you are finishing your macro and want the User to be left at a certain place in the workbook. For actually doing _stuff_ you really don't - and shouldn't - need to use it. – Cor_Blimey Aug 12 '13 at 15:17