I'm seeing that Range.Interior.Color returns different numbers for the same color in some cases, depending on whether it is running in Excel 2007, or Excel 2010 or 2013.
Is that expected?? I'm surprised.
Range.Interior.Color is the background color ("Fill Color") of the cell. In the Immediate pane, you can read it like this:
?ActiveCell.Interior.Color
And set it like this:
ActiveCell.Interior.Color = 10921638
Examples:
Example 1:
(these are the same color though their Range.Interior.Color are different.)
Example 2:
Example 3:
Any suggestions? For now, I'm using conditional compliling to set constants for one number or the other depending on VBA constant VBA7, which returns True for Excel 2010 or later and False for Excel 2007 and earlier:
#If VBA7 Then
'Excel 2010 or later:
Const NO_SHADING_COLOR As Long = 16777215
Const MAIN_HEADER_COLOR As Long = 10921638 'dark gray [in xl2007 s/b 10855845]
Const SUB_HEADER_COLOR As Long = 14857357 'light blue [in xl2007 s/b 14922893]
Const SUBSUB_HEADER_COLOR As Long = 14277081 'medium gray [in xl2007 s/b 14211288]
#Else
'Excel 2007 or earlier:
Const NO_SHADING_COLOR As Long = 16777215
Const MAIN_HEADER_COLOR As Long = 10855845 'dark gray
Const SUB_HEADER_COLOR As Long = 14922893 'light blue
Const SUBSUB_HEADER_COLOR As Long = 14211288 'medium gray
#End If
UPDATE:
Yes, I understand that RGB can be used intead of Range.Interior.Color, and that RGB numbers can be extracted from Range.Interior.Color. But we can do that all day long and still get different sets of RGB numbers depending on Excel version, which effectively takes us back to the original problem.
For any given Range.Interior.Color number, yes it is equivalent to a certain set of RGB numbers. But the point is that depending on Excel version, in some cases you get different Range.Interior.Color numbers for the very same cell without changing the cell's color. If you extract that number to RGB numbers, then you just have different sets of RGB numbers depending on Excel version, which is no better than having the different Range.Interior.Color numbers depending on version.
These cell colors are being set by the user, using Excel's user interface for setting the cell's "Fill Color". The VBA code in this proejct does not set the colors. The VBA code only GETS the colors, and branches according to the color found in the cell being processed.
Aside from obvious exceptions like Application.Version, it isn't normal for object properties to randomly change like this from version to version. Consistency of object properties between versions is an important part of what allows VBA code to work across different versions. If it were not so, we'd have to conditional-compile virtually all our Excel VBA code.
Update:
I haven't been able to reproduce this problem in a new workbook, so it may be workbook corruption. Normally Range.Interior.Color is reliable across versions, for all colors represented.
FWIW, this workbook was sent to me by another person, and that person is on Excel for Macintosh while I am on Windows, so creating the workbook on one platform and then using it on another may have been a factor in the corruption, if any (even though that should work fine).