How can I assign a value to a cell colour in excel?

Question

How can I assign a value to a cell colour in excel?

Answer 1

1

Answer 2

Hi, i am not sure which excel version you are using. I am good at 2003 and can help you at this extent but if you can explore the 2007 version, i am sure you will find the answer because the 2007 version can identify cells based on its colors.



2003, has a functional limitation that it cant identify cells based on colors and give values to the cells or any cells next to it. However it can help you find the cells and you can manually add the values.



For the example you have given in question as A1 for 1 and A2 for 2. Excel has given such numbering to its colors by default and it is called as Color index.



Open a new excel workbook and press "Alt + F11". To your left under "Project - VBA Project" you will find "Sheet 1", double click it and you will find a white screen. Paste the below code there and press F5 and you will find all the colors and color indexes in Sheet1 in excel.



Public Sub Colors_Numbers()

Worksheets(1).Activate

Cells(1, 1).Value = "Color"

Cells(1, 2).Value = "Color Index Number"

For n = 1 To 56

Cells(n + 1, 1).Interior.ColorIndex = n

Cells(n + 1, 2).Value = n

Next n



End Sub



Now you know what color is what index. But if you are finding a way of filtering the cells based on colors then such is not possible in 2003. However, you can find cells that are colored and then go to that cell and make any changes to it.



Press "Ctrl + f" and then click the options button. There would be a format button and a small arrow at the end of the button click that arrow and you click "Choose Format From Cell" and now select the cell which contains the "Yellow" color and press "Find All". what excel does now is it will find all the cells which have the yellow color, by keeping the find box open you can go the cells and give any number.



But in 2007 this Functionality has been advanced. You can filter the cells based on colors.



All the best. Cheers Abdul.

Answer 3

You will need to get into programming code to do this sort of thing.



For a start here is some information about Excel's color palette, and methods used to access it.



http://support.softartisans.com/kbview_1205.aspx

Answer 4

Go to http://www.mrexcel.com register for free and post your question there. It’s an excellent question forum with loads of Excel experts on hand just wanting to help.

Answer 5

If you do not mind using Microsoft's numbering of colors, then you could use these routines to find three separate items that have colors:



Function Xcolor(target As Range) As Integer

Xcolor = target.Interior.ColorIndex

End Function



Function Ycolor(target As Range) As Integer

Ycolor = target.Font.ColorIndex

End Function



Function Zcolor(target As Range) As Integer

Zcolor = target.Borders.ColorIndex

End Function



After adding the above into a Module, you can use a simple formula in your worksheet:



= Xcolor(A4)



.