Instead of resetting the palette manually, could you run this macro the
first time, then manually. I'd be interested to know if it errors.

Sub ColorsReset()
On Error Resume Next
ActiveWorkbook.ResetColors
MsgBox "Error: " & Err.Number
End Sub

Error 0 means no error

Regards,
Peter

"Peter T" <peter_t@discussions wrote in message
...
Slightly curious, the reason I would have expected the orange brown
change
is these are the default colours of color-index 45 in "old" and "new"
palettes respectively. Possibly the palette has been customized, or there
is
another "old" palette I've forgotten about.

You might be able to return the file version like this:

Sub GetFileFormat()
Dim ff, aver, v
ff = ActiveWorkbook.FileFormat
aver = Array(xlExcel2, xlExcel3, xlExcel4, _
xlExcel5, xlExcel7, xlExcel9795)

On Error Resume Next

v = Application.WorksheetFunction.Match(ff, aver, 0)
If Err.Number 0 Then
v = "Later than Excel 97"
Else
v = Choose(v, "xlExcel2", "xlExcel3", "xlExcel4", _
"xlExcel5", "xlExcel7", "xlExcel9795")
End If

MsgBox ff & " : " & v, , ActiveWorkbook.Name
End Sub

Couple of things to try:

Reset the palette (tools / options / color)
Save / close / reopen
Maybe do this a few times, might not work first time, if at all

Save the file like this:
ActiveWorkbook.SaveAs Filename:="MyName.xls", FileFormat:= xlExcel9795

FWIW, I don't think your formats are changing even though it looks that
way,
but the default palette displays different colours in different versions
(from memory 19 out of the 56 are different).

I'd be interested in your feedback.

Regards,
Peter

"John Pritchard" wrote in
message
...
Thanks for taking an interest! If I save as the "latest version" then
orange
remains orange - But if I save as the old format orange is changed to
yellow
when I reopen. Do you happen to know how to get the excel version a file
was
created in ?

"Peter T" wrote:

Not sure if this is related but I've come across some curious problems
linked to the difference in default palettes in pre XL97 and XL97 on.

By chance, if you format a colour to orange (right of red on the
palette),
does it come back a sort of brown after save / close / reopen.

Regards,
Peter

"John Pritchard" <John wrote in
message
...
I save a workbook with workbooks(<filename).close savechanges :=
TRUE
and
any cell formatting is lost. I've traced the circumstances to the
version
of
Excel the original <filename was created in. If the file was
version
4.0
formatting is lost (even if it's been subsequently saved as the
'latest
Excel
version') otherwise it's OK. Given that I can have no influence on
the
Excel
version of the original file is there anyway of saving my formatting
changes
(I'm only interesting in the colorindex). Also how can I tell what
the
original Excel version was from within VB (or elsewhere) - The above
applies
to XP, NT and windows 2000. I'm using office 2000 in all cases.
Thanks.