up vote 0 down vote favorite
share [g+] share [fb]

Is it possible to set the color of a cell based upon a hex string (e.g., "RRGGBB") or a numeric value and if so how? I have a sheet that converts colors that are stored in BGR format to RGB. I would like would to set the cell color next to the converted RGB value.

link|improve this question
1  
What version of Excel? 2003, 2007, or 2010? – Velociraptors Oct 15 '10 at 2:09
@Mehper I think it's a different question. – Sux2Lose Oct 15 '10 at 11:29
@Sux2Lose: I thought conditional formatting answers lordhog's question, doesn't it? – Mehper C. Palavuzlar Oct 15 '10 at 11:35
Conditional formatting actually is the way to go about it. – Sathya♦ Oct 15 '10 at 18:06
show 2 more comments
feedback

1 Answer

up vote 3 down vote

You have to use VBA to accomplish your goal. Feel free to modify the routine below, which colors the background of the current cell, according to your needs.

Sub ColorGrey()
    ActiveCell.Interior.Color = RGB(100, 100, 100)
End sub
link|improve this answer
I am getting the error 1004 ("Application-defined or object-defined error") when I use the above code. – lordhog Oct 18 '10 at 18:55
@lordhog, I tested it in Excel 2003 and it worked great, but you can probably peruse the 2007 help files to see if the syntax has changed. Remember that a cell would probably have to be selected for ActiveCell to work. – Lance Roberts Oct 18 '10 at 19:55
Also works in 2007. Nice job. – Sux2Lose Oct 19 '10 at 18:10
Obviously, I doing something wrong. I create a function like "Public Function SetCellColor(r As Integer, g As Integer, b As Integer)" with the code listed, then for a given cell I use "=SetCellColor(HEX2DEC(A1), HEX2DEC(A2), HEX2DEC(A3) )". I will get error 1004. Strange, if I use "ActiveCell.Cells.Borders.Color = RGB(r, g, b)" instead of the code listed it will change the active cell's border color, but it is not changing the cell that has the equation in it. I think error 1004 (from reading) is due to the cell not being activated. I am looking for the cell with the equation to change color – lordhog Oct 20 '10 at 18:54
Have you tried to use Activesheet.Range("A1") in place of ActiveCell? What does it happen? – Toc Oct 22 '10 at 20:25
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.