Cell Colors

[jpmeadors]

I have a row of 10 cell with 7 different colors and I need to determine the "Color Index" number for each cell in the cell below. A1:A10 have 7 diff colors - B1:B10 refer to the "Color Index" number associated with the cell above.


I would rather not use vba.

 

[Hui]

Jpmeadors


Don't be put of by VBA, it can be your friend and open up a whole new world of what is possible.


For your problem you can use a simple User Defined Function (UDF) to achieve what you want


Copy and paste the following code into the VBA Window

Alt F11

Right Click on your project and Insert Module

Paste the following code into the Right Hand Window

[pre]

Function myColor(mycell As Range, myType As Variant) As Variant
If UCase(myType) = "BG" Then
myColor = mycell.Interior.Color
ElseIf UCase(myType) = "FG" Then
myColor = mycell.Font.Color
Else
myColor = "Invalid type."
End If
End Function

[/pre]

To use it enter

=mycolor(A10,"BG") 'for the Background Color

=mycolor(A10,"FG") 'for the Font Color


You can also do something like

=if( mycolor(A10,"BG")=60000, A1+10, A1*2)

If the background color = 60000 then A1+10 else A1*2

 

[jpmeadors]

Thank you!

VBA feels foreign to me and I tend to avoid it. You set it up well. What I am trying to do is determine the color of a cell in a row and convert the color to a reference number 1-x. Then I want to do a bubble chart of the reference numbers. I am trying to convert an image in a Pixel form, square cell to a round or Bubble shape.


Any ideas?


Jason

 

[Hui]

Jason

You could break the colors into Ranges

The Highest Color number is 16,646,655

So if you wanted 10 Ranges use

` - 1,664,666

1,664,666 3,329,331

3,329,331 4,993,997

4,993,997 6,658,662

6,658,662 8,323,328

8,323,328 9,987,993

9,987,993 11,652,659 ...'

etc

and then use simple lookup to get your Ref No.