-1

I cannot make this line work:

Range(Cells(Jour, 13), Cells(Jour, 18)).Interior.Color = 7

Each time this error message pop-up

Run-time error '1004': Application-defined or object defined error

But This line work:

Range(Cells(Jour, 13), Cells(Jour, 18)).Select

I saw lots of example with Interior.Color = "Whatever" after range.

Why it doesn't work?

Thank you

Mi-Creativity
  • 9,554
  • 10
  • 38
  • 47
user1626749
  • 35
  • 4

2 Answers2

1

Here this should work thanks to @Jeeped for pointing out the error.

Sub color()
Dim Jour As Long
Dim wk As Worksheet

Jour = 1
Set wk = Sheet1 'Change it with your sheet number.

With wk
    .Range(.Cells(Jour, 13), .Cells(Jour, 18)).Interior.color = 7
End With

End Sub
Stupid_Intern
  • 3,382
  • 8
  • 37
  • 74
  • 1
    Actually, there is no explicit relationship between the [Range.Cells](https://msdn.microsoft.com/en-us/library/office/ff196273.aspx) and the parent worksheet; only implicit to the [ActiveSheet](https://msdn.microsoft.com/en-us/library/office/ff822753.aspx?f=255&MSPPError=-2147217396) which may or may not be **Sheet1**. There is for the [Range object](https://msdn.microsoft.com/en-us/library/office/ff838238.aspx) but not `Cells` defining the range's scope. See [this](http://stackoverflow.com/questions/27763089/count-the-number-of-rows-in-another-sheet/27763394#27763394) for an example. –  Feb 07 '16 at 08:46
  • Ohh yes you're right I got an error when I added a new sheet in the workbook even though there was a worksheet named "Sheet" thanks I will modify it – Stupid_Intern Feb 07 '16 at 09:07
  • That looks great! Now the OP just has to figure out whether he/she wants a fill with `.Color = 7` (black) or a `.ColorIndex = 7` (purple see [ColorIndex table](https://public.blu.livefilestore.com/y1pBBNZK8-zzD6XAF0RdBv0bzYf7zy6gQeJNLfNFErA0AOqVgdSDYitBxntpEGzTO53lON4-9iLXwLHCw8xlYGdKQ/ColorIndexColors.PNG)) –  Feb 07 '16 at 09:20
0

If you set up Jour properly, then this should work.

Sub jour_Jour_Banks()
    jour = 1
    Range(Cells(jour, 13), Cells(jour, 18)).Interior.Color = 7
End Sub
Davesexcel
  • 6,896
  • 2
  • 27
  • 42