VBA to color Active.Cell with Arrow key syntax

Topic

New Reply

Although it’s part of a recorded macro to obtain the color number, is there anything else required in the VBA coding ?
It has to use the ActiveCell.Offset(0, -2) part because the Cell location is not constant, it is where it is at the time.
run time error 438- Object does not support this property or method

Application.DisplayAlerts = False
ActiveCell.Offset(0, -2).Color = 65535 ‘yellow

Thanks

Reply | Quote

Replies

XP,

Amend your code to:

Code:

Sub Macro1()
Application.DisplayAlerts = False
ActiveCell.Offset(0, -2).Interior.Color = 65535 ‘yellow
End Sub

You will receive an error if the column number of the active cell is 1 or 2 because of your offset to the left.

HTH,
Maud

Reply | Quote

Hi XP

If you are using Excel2007 or later, the attached file may be useful.
It has the ‘color number’ for vba names you can use in your code.
These are sorted alphabetically by name, and by ‘color number’ value.

So, for example, you could use

Code:

ActiveCell.Offset(0, -2).Interior.Color = rgbYellow

or

Code:

ActiveCell.Offset(0, -2).Interior.Color = rgbGold

..What I would like is for someone to sort this colour list into ‘rainbow’ order!
Maud – fancy the challenge? Anyone?

zeddy

Reply | Quote

Zeddy,

I’ll leave that for you since you’ll probably figure out some vba code to do it automatically.

Reply | Quote

Maud,

Trap the error:

Code:

Sub Macro1()
     Application.DisplayAlerts = False
     With ActiveCell
           if .Column() > 2 then _
             .Offset(0, -2).Interior.Color = 65535 'yellow
     End With  'ActiveCell
End Sub

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG,

That’s the perfect way to do it!

Zeddy,

Here is the full spectrum of colors with their RGB and Values in increments of 10.

Maud

Reply | Quote

RG,

That’s the perfect way to do it!

Zeddy,

Here is the full spectrum of colors with their RGB and Values in increments of 10.

Maud

Reply | Quote

Hi Maud

..thanks for that spectrum. I’ll add that to my toolbox.

I’m posting a new thread on this.

zeddy

Reply | Quote

Thanks
:rolleyes:

Sub macrodfdsc()

‘ Interior.Color = 65535
‘ActiveCell.Offset(0, -2#).Interior.Color = 65535

Selection.Offset(0, -3).Resize(1, 8).Interior.Color = 65535 ‘*Down*
End Sub

Selection.Offset(0, 1).Select ‘*Right*
Selection.Offset(0, -1).Select ‘*Left*
Selection.Offset(-1, 0).Select ‘*Up*
Selection.Offset(1, 0).Select ‘*Down*

Selection.Offset(2, 0).Resize(5, 8).Select ‘*Down*

Reply | Quote