Reading excel color information into VB.NET

[Antoine]

Hello,

Is there a possible way to get the back and foreground color form an excel cell ?

For instance:
The backcolor of the cell is made red
The forecolor of the cell is made blue

Is there a way to get these values into variables for one cell ? I allready managed to read data from an excelsheet. But now I also want to try to get other attributes.

Who can help me ?

Regards,
Antoine

 

[Mike_R]

Unfortunately, color in Excel is very poorly done. Techincally, you can utilize the Range.Interior.Color property to get the RGB value for the BackColor and Range.Font.Color property for the ForeColor. However, Excel does not *really* use RGB and so trying to use the .Color property almost always creates a mess.

What you have to do in Excel, is to utilize the .ColorIndex Property, which Set/Gets a value 0 to 56 from the Workbooks color palette.

xlRng.Interior.ColorIndex will Set/Get the BackColor index and xlRng.Font.ColorIndex will Set/Get the ForeColor Index.

Now what do these Index values map to? Well, if you open Excel and choose Alt|Tools|Options... and then the Color Tab, youll see the Pallette. And you can customize these colors as well.

Customization is nice, but is also the source of the trouble when using RGB. If you or the User has customized any of these values, Excel seems ignorant of these changes when effecting the .Color property. That is, if you give a RGB value of RGC(255,0,0) which is pure Red, Excel will find the closest match within the Color Pallette and execute that ColorIndex value. So far so good... The problem is that if one has customized/changed the Red cell to be Blue, then passing in .Color = RGB(255,0,0) will return that same Color Index value! And -- you guessed it -- the result will be Blue!

So, use Range.Interior.ColorIndex and Range.Font.ColorIndex when dealing with Excel Color, but be aware that each Workbook can have its own customized Color Pallete, so its best to utilize this on a Workbook that has a known color palette.

I hope this made sense!
,
Mike

 

[Antoine]

Dear Mike,

Thanks for your reply, at least now it is clear to me why all these strange things happened .

I wonder WHEN microsoft is going to make this all even in their software

At least, thank you very much for your explanation !

Regards,
Antoine