setting interior color - error

[Guest]

I have some code including the following statement. It worked fine for months
but all of a sudden I'm getting an error message from it. I have not changed
the names of the worksheets or locations of the cells. The With statement
this is in the middle of represents the "Crew" worksheet.

activeworkbook.worksheets("Crew") .Range(.Cells(2, 12), .Cells(4,
12)).Interior.Color = RGB(180, 180, 180)

"Unable to set the Clor property of the interior class."

Any ideas on why this suddenly no longer works?

Thanks,
hanshil

 

[Guest]

You're not showing us the With or End With statements.
ActiveWorkbook.Worksheets("Crew") would be part of the With statement. When I
rewrote your code as follows, it executed perfectly in Excel 2003:

With ActiveWorkbook.Worksheets("Crew")
.Range(.Cells(2, 12), .Cells(4, 12)).Interior.Color = RGB(180, 180, 180)
End With

Hope this helps,

Hutch

 

[Guest]

Hutch,

code as I have it presently:

With ActiveWorkbook
.Names.Add Name:="st", RefersToR1C1:="=Crew!R2C12"
.Names.Add Name:="ot", RefersToR1C1:="=Crew!R3C12"
.Names.Add Name:="dt", RefersToR1C1:="=Crew!R4C12"
With .Worksheets("Crew")
.Range(.Cells(2, 12), .Cells(4, 12)).Interior.Color =
RGB(180, 180, 180)
.Range(.Cells(2, 13), .Cells(4, 13)).Interior.ColorIndex
= xlColorIndexNone
End With
End With

It has no problem with the defined names but it chokes on the color. Used to
work just fine but then suddenly decided it didn't like it. Its doing the
same thing on another machine.

Thanks,
Hans

 

[Peter T]

I can't see any obvious reason why that would fail, unless you've protected
against format changes or as a very long shot have a missing references
(Tools > references).

As a temporary fix in case of a missing ref' try
..Color = VBA.Information.RGB(180, 180, 180)
..Color = 12632256
..Color = -4142&

FWIW this grey does not exist in the default palette. Unless you've
customized a colour it will map to the nearest it can find, probably
colorindex 15 / rgb(192, 192, 192)

Regards,
Peter T

 

[Guest]

there it is, thanks. silly me, I protected the sheet from format changes and
forgot to code in to lock & unlock it.
thanks.