Give a value to the color of a cell

[Sebastian]

Hi, I am trying to use a conditional to get a value from another cell. i.e.

A1 background color is yellow. in B1 I would write something like
if(A1=yellow,1,0) to get a value 1 in the cell B1, or 2 for blue, etc...

Thank you for your help.

 

[Mike H]

Hi,

You can do it with a UDF.
Alt +F11 to open VB editor. Right click 'This Workbook' and insert module
and paste the code below in. The code is easy to update to addd extra colours
and if you not sure what the numbers are record a macro of yourself colouring
cells and use the numbers to add additional cases.

Call with
=BackColour(A1)


Function BackColour(r As Range)
Select Case r.Interior.ColorIndex
Case Is = 6 'Yellow
BackColour = 1
Case Is = 5 'Blue
BackColour = 2
Case Is = 4 'Green
BackColour = 3
Case Is = 3 'red
BackColour = 4
Case Is = 53 'Brown
BackColour = 5
Case Else
BackColour = "Not Defined"
End Select
End Function

 

[Gord Dibben]

How did A1 get to be yellow?

If by Conditional Formatting, use the CF condition as criterion.

If manually colored yellow, you will need VBA function.

See Chip Pearson's site for that.

http://www.cpearson.com/excel/colors.aspx


Gord Dibben MS Excel MVP

 

[Cassie]

I have this same issue. The background color is manually applied. My
example is if cell B6 is yello - I want 1 to populate in E6.

Can you tell me how to do the VBA function to achieve that?

Thanks

 

[Gord Dibben]

I'm sure you have a grander plan in mind, but per your example.......

Sub enter_1()
If Range("B6").Interior.ColorIndex = 6 Then
Range("E6").Value = 1
End If
End Sub

Post back with the rest of the details for a more complete reply.


Gord Dibben MS Excel MVP


On Fri, 6 Feb 2009 07:34:08 -0800, Cassie <Cassie