Excel Reports Wrong Border Color

[Bob]

In VBA, this code

ActiveCell.Borders(xlEdgeBottom).Color

returns the original pallette color rather than the custom
color the user set that pallette color to be. The
spreadsheet displays the desired custom border color but
the code returns only the original color.

Saving/closing the workbook/Excel does not affect the
problem.

How do you get the actual displayed color of a border
line? BTW, the environment is XL97.

Tx

 

[Sandy V]

Returning .Color of a cell colour format gives the Long
value of the ColorIndex used for the format, ie 1 of the
56 workbook palette colours.

If you apply .Color as a format (rather than a
Colorindex), if Excel cannot find an exact match in the
current palette it will apply what it calculates is the
closest colour match from the palette.

Thus returning .Color may not be the same as the .Color
that was applied, or rather what was attempted to be
applied. In other words your code is correctly returning
the colour, which may or may not be not be what was
applied.

If you want to customize a colour then do so direct to the
palette, then apply the ColorIndex as the format. Maybe
this will demonstrate what's going on:

Sub Test()
'2nd time comment next line
ActiveWorkbook.ResetColors 'default palette
mycolor = RGB(240, 20, 120)
Cells(1, 2) = mycolor

Cells(2, 1).Interior.Color = mycolor
Cells(2, 2) = Cells(2, 1).Interior.Color
Cells(2, 3) = Cells(2, 1).Interior.ColorIndex

ActiveWorkbook.Colors(6) = mycolor
Cells(3, 1).Interior.ColorIndex = 6
Cells(3, 2) = Cells(3, 1).Interior.Color
Cells(3, 3) = Cells(3, 1).Interior.ColorIndex
End Sub

Regards,
Sandy

PS
This is new - now MS's newsreader is automatically giving
me an an anonymous address. Perhaps just as well as in the
last 4 weeks I've been bombarded by several Mb/day of spam
purporting to originate from MS.
savituk yahoo co uk