Is It Possible to Set Cells Own Color

Is it possible to have a cell set it's own background color through the use of a VBA formula?

Something Like:

=FillMeAs(234,23,164)

...to fill a the cell containing this formula with the RGB color (234,23,164) ?

If not is it possible to have the formula in one cell cause the fill of another neighboring cell to have the specified backgroung color?

If this is not possible via formulas like this is it possible to write some VBA code that just does it for some cell?

I am trying to design a spreadsheet that can have cells display certain color tables according to an algorithm of my choosing.

Michael Karas

Interesting issue. Is it possible to set cell color to anything outside the color index?
In not, is it possible to create you own "color index"?
I opened paint and had a look at "define custom colors", mayby it's possible to create a similar template for office?
Anyone that have some proper ideas on this issue?

PS.
I would go for an existing color index :D
# 7 possibly close enough? :chuckle:


Sub check_color_index()

For i = 1 To 56
Sheets(2).Cells(i, 3) = i
Sheets(2).Cells(i, 4).Select

With Selection.Interior
.ColorIndex = i
.Pattern = xlSolid
End With
Next i
End Sub

The rule of thumb is that you can't use a VBA UDF called by a worksheet range (ie, in a formula) to change the Excel environment. There are a few edge case exceptions to this, but range interior colour isn't one of them. The restriction on the variety of colours depends on your Excel version.

Hope that helps...

I played around with coloring cells in Excel 2007. To get around the issue of trying to use a cell formula that invokes a UDF to change a worksheet contents I put the code instead into a sub that gets called from a button press. I just associated the call to the subroutine to the button's macro link. This now gets me to my code in the VBA page.

I find that using the Cells(r,c).Interior.Color=RGB(R, G, B) does not actually get the cell filled with exactly the color I selected. Instead it gets filled with some "close" color from those in Excel's 56 color pallet. This means that trying to use Excel to try to make color tables where I want to be able to select the colors as any color in the 24-bit RGB color space is not easily possible.

Mike Karas

Just a long shot and awkward thought. Would it be possible to save a pic (.png or similar) with the cell size and correct RGB and insert that file on every cell fulfilling your criteria?
Or would that pic “misfit” the cell? Off course the actual file could be larger than necessary… :D

Excel 2007 does support real cell coloring and by not abusing the color index.
I don't have Excel 2007 by hand now, will try it at the office later.

http://msdn.microsoft.com/en-us/library/cc296089(office.12).aspx#xlDiscoveringColorIndex_ColorIndexVsColor

I am in the process of exploring a different solution. My goal was to try to make a spreadsheet that would allow users to generate sets of 125 colors based upon some particular criteria. These are meant to be used to experiment with algorithms to order the colors in the list in useful and pleasing ways.

My current attack is to add a list of label controls in a column that get filled from some VBA code using the .BackColor property.

Michael Karas

Flyguy:
I wrote some VBA code Subs (triggered from buttons on the worksheet) to fill a list of cells in a column with specific 24-bit colors using the .Color property. I then had another Sub extract the .ColorIndex property and display that in an adjacent column. It was on the basis of the displayed color index values that I deduced that I was not getting the actual colors that I had requested. The web page that you linked seems to indicate that the requested .Color does indeed get used but that the .ColorIndex property gets set to a value that corresponds to the near palette color.

I will enhance this spreadsheet to add some more code to then re-display the retreived color index values in another column of color filled cells to see what the differences between what my requested colors are and the palette colors look like. After I get that finished I'll post the result here to share.

All in all this is an interesting subject. I am still disappointed that one cannot use the UDFs and Cell Formulas to cause the cell formatting to change. There must be some underlying complicated technical reason this cannot be supported due to how spreadsheet re-calc works and how they try to prevent endless loops in the re-calc process.

Michael Karas

disappointed that one cannot use the UDFs and Cell Formulas to cause the cell formatting to change. There must be some underlying complicated technical reason this cannot be supported due to how spreadsheet re-calc works and how they try to prevent endless loops in the re-calc process.

It's deliberately imposed, and I think it's a very good thing that it is. If cell formulas could change the Excel environment rather than just returning a result, then it would create absolute havoc!

You can use conditional formatting, if that's what you mean?

I agree that if UDFs starting having side effects it would indeed create a very bad scene.

I am not actually after conditional formatting.

I have attached the spreadsheet promised in the previous posting. FOllowing the info that FlyGuy linked I can see that for one particular algorithm of creating a table of 125 colors that are spread evenly from RGB=(0,0,0) to RGB=(255,255,255) that there are some colors that fill in with the .Color property very similar to the close .ColorIndex palette color and others where there is a distinct difference.

Note that the attached spreadsheet is the .XLSM type supported under Excel 2007. It is embedded into a ZIP file so that it can be attached here.

Michael Karas