Populating cell by color format-IF statement?

[kdsteff]

Trying to return a 1 or 2 by the color format on a cell. So if A1 is yellow
format, then return a 1 otherwise a 2. Possibilities please?

 

[Shane Devenshire]

Yes, but you need to use VBA, in other words you need to program it, you
can't use any spreadsheet formulas for this.

 

[kdsteff]

Ok. I am not a program writer. Can you help with the code? Must have to
use something with the color index? And then setup a macro?

 

[Gord Dibben]

First you must copy/paste this Chip Pearson UDF to a general module

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange.Font.ColorIndex
Else
CellColorIndex = InRange.Interior.ColorIndex
End If
End Function

Then use a formula

=IFcellcolorindex(A1)=6,1,2)


Gord Dibben MS Excel MVP

 

[ebloch]

The free ASAP Utilities has functions that use Cell Color.

Eric