Color Indexing

[PBANKS]

Is it possible to have a formula check the color index of a cell?

Example if A1 color index = 6 then X

[Bryan Hessey]

Is it possible to have a formula check the color index of a cell?

Example if A1 color index = 6 then X

Please Login or Register  to view this content.

works for me.

---

[JR@SGC]

Yep.

heres the simplist you can modify, it checks if the cel color is yellow and if it is selects b1

Sub ooo()
If Range("a1").Interior.ColorIndex = 6 Then
Range("B1").Select
End If
End Sub

For reference heres a small list of the color number index

1 = black
2 = white
3 = red
4 = bright green
5 = blue
6 = yellow
7 = pink
8 = bright blue
9 = brown
11 = dark blue
13 = teal


hope it helps

[PBANKS]

Thanks, I will try this in just a bit.

[PBANKS]

Sorry, I actually wanted this for a simple formula that is in the cell itself. I was in a hurry earlier and did not pay attention. I believe that I have done this before, but I just can not figure it out.

[Kaak]

Function CINDEX(ByVal rRange as Range) as integer

If rRange.Cells.count = 1 then CINDEX = rRange.Font.ColorIndex

End Function

If you put something like this in a module you can use the function on your worksheet

[PBANKS]

Sorry, but I am having one of those moments where I just do not understand. How do I use it in my formula?

If A1 = X then CINDEX A1 = 2?

[PBANKS]

Does anyone know the magic to this, I can not figure it out. I see where it shows up in the functions, but how do I make use of it?

[Richard Schollar]

Hi

An alternative way that doesn't use VBA is to make use of an XLM4 macro function:

1.Go Insert>Name>Define and name the formula you are about to create CellCol
2. In the refers to box type:

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))

and click OK.

3. Now, back in your worksheet in the cell to the immeriate right of the one you want to return the color index for, type

=CellCol

and hit return. It should return a value - you can refer to the value list that JR@SGC provided you with for what the numbers mean.

Incidentally, the UDF code (which goes into a standard module) can be used in a cell like any other:


=CINDEX(A1)

to return the color index of cell A1.

Hope this helps!

Richard

[PBANKS]

Thanks Richard, that works. The only problem is that I do not need it right next to the cell. I tried what KaaK suggested, but it gives the same results for every cell I test. I really appreciate everyones help, this gets it one step closer.

Thank You