0

I'm getting

1004 error

while running the below macro, in line
Worksheets("Testable").Range(Range("A2"), Range("A2").End(xlDown)).Select

Sub colour()
    Dim cell As Range

    Worksheets("Testable").Range(Range("A2"), Range("A2").End(xlDown)).Select
    For Each cell In Selection

        cell.EntireRow.Interior.ColorIndex = 33
    Next cell
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Maitreya
  • 69
  • 1
  • 2
  • 8
  • What's the goal? Do yu just want to make column A blue from row `2` to the bottom of the data? You don't need a loop to do that. – ashleedawg Apr 26 '18 at 11:47
  • Actually no, it looks like you're trying to to make all of every row blue, except for Row 1. You need to carify your goal please. Answers from my or anyone else are only guessing what you need to do. – ashleedawg Apr 26 '18 at 11:49

2 Answers2

3

First Select or Activate the worksheet. Then Select the range.

Here is a working example:

Sub colour()
    Dim cell As Range
    Worksheets("Testable").Activate
    Worksheets("Testable").Range(Range("A2"), Range("A2").End(xlDown)).Select
    For Each cell In Selection
        cell.EntireRow.Interior.ColorIndex = 33
    Next cell
End Sub

enter image description here

EDIT#2:
To avoid both looping and selection, use something like:

Sub colourAlternative()
    Dim cell As Range
    With Worksheets("Testable")
        Set cell = Range(.Range("A2"), .Range("A2").End(xlDown)).EntireRow
    End With
    cell.Interior.ColorIndex = 33
End Sub

There were three things at issue here:

  1. why Select failed
  2. avoiding Select
  3. avoiding the loop
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 2
    well, better avoid select at all. – Pᴇʜ Apr 26 '18 at 11:47
  • @Pᴇʜ I completely agree with you. – Gary's Student Apr 26 '18 at 11:47
  • Hi, Now i have changed the code as below. But still receiving error as: "Unable to get the Interior Property of the range class" Sub colourTestable() Dim cell As Range Worksheets("Testable").Select Range(Range("D2"), Range("D2").End(xlDown)).Select For Each cell In Selection cell.EntireRow.Interior.ColorIndex = 33 Next cell End Sub – Maitreya Apr 26 '18 at 11:56
  • I need to use the select as i need to colour code the rows till which the data exists. – Maitreya Apr 26 '18 at 11:59
  • 1
    I don't understand how/why you guys pick-and-choose which poorly written, incomplete questions you're going to answer and which you're going to flag. – ashleedawg Apr 26 '18 at 12:07
  • @Maitreya no, you can also do this without select. There is no need to select something in Excel if you just want to change the color this works also without using select. – Pᴇʜ Apr 26 '18 at 12:08
  • @Maitreya Please don't add code in comments, you can [edit] your question if you need to add more information. Also it would be a good idea to check out "[ask]" as well as "[mcve]". – ashleedawg Apr 26 '18 at 12:09
  • @Maitreya See my **EDIT#2** – Gary's Student Apr 26 '18 at 12:20
  • Thanks a lot @Gary'sStudent foor your effort and answer. – Maitreya Apr 26 '18 at 12:32
1

Below you will find a simple and foolproof (based on the details in the original question) way to accomplish your task:

With Worksheets("Testable")
    .Range(.Range("A2"),.Range("A2").End(xlDown)).EntireRow.InteriorColorIndex = 33
End With

The reason you get an error in the line 

Worksheets("Testable").Range(Range("A2"), Range("A2").End(xlDown)).Select

is because the two Range("A2") parts of the statement are unqualified and Excel does not know what to do with them (especially if the Testable sheet is not the active sheet). By qualifying them in the way I did above, you avoid selecting the object and work directly with it, thus optimizing the code execution.

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • Thanks a lot Scott for the explanation. I think it was just because the worksheet was not active while selecting the range. Your code worked perfectly. Thanks again. – Maitreya Apr 26 '18 at 12:30
  • 1
    @Maitreya Yes. But the point is that in this case (and in most cases) it's *not necessary* for the sheet to be selected / active and using that style of coding can slow down your code. There are *certain* cases where it is necessary to select / activate a sheet, but they are few. – Scott Holtzman Apr 26 '18 at 13:24