Formula to test for cell color?

[Houstonwolf]

Hello. I thought this might be interesting. Is there formula to use if I want an IF statement to add a date "TODAY()" to a cell if a corresponding cell is highlighted? I would search for this in the archives if I had a clue what to search for. "Conditional formatting", maybe?

In Col J "Appt. Date", I want to add today's date if the cell in Col C of that row is green.

How would that work? My worksheet is posted below:

HP Daily report 10SEP2008.xls
	C	D	E	F	G	H	I	J
1	Container#	ArrivalatPortofDischarge(Berth)	TerminalDeparture	RailDeparture	RailArrivalatMemphis	ExpectedDeliveryDueDate	IPN/CMR/OCMR	Appt.Date
2	CAXU8113590	8/29	xxx				D750
3	FCIU8310110	8/29	xxx				D750
4	FSCU9824371	8/29	xxx				D750
5	HJCU1910040	8/29	xxx				D750
6	HJCU1406065	9/3	xxx				D750
7	HJCU1532567	9/3	xxx				D750
8	CAIU2421572	9/3	xxx				D750
9	GLDU7584720	9/3	xxx				D750
Memphis, TN.

Thanks, everyone!

 

[Jonmo1]

Are the cells in column C formatted green by conditional formatting ?

 

[Oaktree]

Assuming, per jonmo's question, that column C is formatted *without* conditional formatting, try this:

1) go to insert --> name --> define and define the named range Col_C_Color to refer to =GET.CELL(63,INDIRECT("RC3",FALSE))

2) J1=IF(Col_C_Color = 4, TODAY(),""), copied down and format column J as a date

 

[Houstonwolf]

Are the cells in column C formatted green by conditional formatting ?

No, the original user modified the format for the three entries highlighted.

 

[Jonmo1]

No, the original user modified the format for the three entries highlighted.

Then Oaktree's method is probably your best bet.

 

[Houstonwolf]

Assuming, per jonmo's question, that column C is formatted *without* conditional formatting, try this:

1) go to insert --> name --> define and define the named range Col_C_Color to refer to =GET.CELL(63,INDIRECT("RC3",FALSE))

2) J1=IF(Col_C_Color = 4, TODAY(),""), copied down and format column J as a date

Works perfectly! What exactly have I just done? What does the '4' refer to?

 

[Oaktree]

The 4 is because 4 is the color index that corresponds to the shade of green you used to highlight column C. If you used red, it would be 3, yellow would be 6, etc.

If you're interested, PaddyD posted a nice writeup on the GET.CELL approach (and all of the things you can use it for) here:

http://www.mrexcel.com/forum/showthread.php?t=20611

 

[Kenneth Hobson]

If you want to add this to a Module, you can use this UDF. Replace the A1 with your lookup formula. A UDF like ColorIndex(), will only update when a calculation occurs.

The colorindex's are shown at the end for your information.

Of course this method does not check conditional format colorindexes.

'=IF(ColorIndex(A1)=6, TODAY(),"")
Function ColorIndex(Cell As Range, Optional sAttrib As String = "i") As Long
  'sAttrib: f=Font, i=Interior, b=Border
  Dim s As String
  s = LCase(sAttrib)
  Select Case s
    Case "i"
      ColorIndex = Cell.Interior.ColorIndex
    Case "b"
      ColorIndex = Cell.Borders.ColorIndex
    Case Else
      ColorIndex = Cell.Font.ColorIndex
  End Select
End Function

'http://vbaexpress.com/forum/showthread.php?t=22084
Public Enum xlColorIndex
    xlCIBlack = 1
    xlCIWhite = 2
    xlCIRed = 3
    xlCIBrightGreen = 4
    xlCIBlue = 5
    xlCIYellow = 6
    xlCIPink = 7
    xlCITurquoise = 8
    xlCIDarkRed = 9
    xlCIGreen = 10
    xlCIDarkBlue = 11
    xlCIDarkYellow = 12
    xlCIViolet = 13
    xlCITeal = 14
    xlCIGray25 = 15
    xlCIGray50 = 16
    xlCIPeriwinkle = 17 '-----------------------------
    xlCIPlum = 18 ' chart colours
    xlCIIvory = 19 '
    xlCILightTurquoiseChart = 20 '
    xlCIDarkPurpleChart = 21 '
    xlCICoralChart = 22 '
    xlCIOceanBlueChart = 23 '
    xlCIIceBlueChart = 24 '
    xlCIDarkBlueChart = 25 '
    xlCIPinkChart = 26 '
    xlCIYellowChart = 27 '
    xlCITurquoiseChart = 28 '
    xlCIVioletChart = 29 '
    xlCIDarkRedChart = 30 '
    xlCITealChart = 31 '
    xlCIBlueChart = 32 '-----------------------------
    xlCISkyBlue = 33
    xlCILightGreen = 35
    xlCILightYellow = 36
    xlCIPaleBlue = 37
    xlCIRose = 38
    xlCILavender = 39
    xlCITan = 40
    xlCILightBlue = 41
    xlCIAqua = 42
    xlCILime = 43
    xlCIGold = 44
    xlCILightOrange = 45
    xlCIOrange = 46
    xlCIBlueGray = 47
    xlCIGray40 = 48
    xlCIDarkTeal = 49
    xlCISeaGreen = 50
    xlCIDarkGreen = 51
    xlCIBrown = 53
    xlCIIndigo = 55
    xlCIGray80 = 56
End Enum

 

[Houstonwolf]

The 4 is because 4 is the color index that corresponds to the shade of green you used to highlight column C. If you used red, it would be 3, yellow would be 6, etc.

If you're interested, PaddyD posted a nice writeup on the GET.CELL approach (and all of the things you can use it for) here:

http://www.mrexcel.com/forum/showthread.php?t=20611

Thanks, Tree. I can find a lot of uses for this in my daily job. I just found another way to use this formula.

People at work think I'm smart...

 

[Houstonwolf]

Thanks, Kenneth. This might be a little too extensive for me right now, but I look forward to trying it someday.

If you want to add this to a Module, you can use this UDF. Replace the A1 with your lookup formula. A UDF like ColorIndex(), will only update when a calculation occurs.

The colorindex's are shown at the end for your information.

Of course this method does not check conditional format colorindexes.

'=IF(ColorIndex(A1)=6, TODAY(),"")
Function ColorIndex(Cell As Range, Optional sAttrib As String = "i") As Long
  'sAttrib: f=Font, i=Interior, b=Border
  Dim s As String
  s = LCase(sAttrib)
  Select Case s
    Case "i"
      ColorIndex = Cell.Interior.ColorIndex
    Case "b"
      ColorIndex = Cell.Borders.ColorIndex
    Case Else
      ColorIndex = Cell.Font.ColorIndex
  End Select
End Function

'http://vbaexpress.com/forum/showthread.php?t=22084
Public Enum xlColorIndex
    xlCIBlack = 1
    xlCIWhite = 2
    xlCIRed = 3
    xlCIBrightGreen = 4
    xlCIBlue = 5
    xlCIYellow = 6
    xlCIPink = 7
    xlCITurquoise = 8
    xlCIDarkRed = 9
    xlCIGreen = 10
    xlCIDarkBlue = 11
    xlCIDarkYellow = 12
    xlCIViolet = 13
    xlCITeal = 14
    xlCIGray25 = 15
    xlCIGray50 = 16
    xlCIPeriwinkle = 17 '-----------------------------
    xlCIPlum = 18 ' chart colours
    xlCIIvory = 19 '
    xlCILightTurquoiseChart = 20 '
    xlCIDarkPurpleChart = 21 '
    xlCICoralChart = 22 '
    xlCIOceanBlueChart = 23 '
    xlCIIceBlueChart = 24 '
    xlCIDarkBlueChart = 25 '
    xlCIPinkChart = 26 '
    xlCIYellowChart = 27 '
    xlCITurquoiseChart = 28 '
    xlCIVioletChart = 29 '
    xlCIDarkRedChart = 30 '
    xlCITealChart = 31 '
    xlCIBlueChart = 32 '-----------------------------
    xlCISkyBlue = 33
    xlCILightGreen = 35
    xlCILightYellow = 36
    xlCIPaleBlue = 37
    xlCIRose = 38
    xlCILavender = 39
    xlCITan = 40
    xlCILightBlue = 41
    xlCIAqua = 42
    xlCILime = 43
    xlCIGold = 44
    xlCILightOrange = 45
    xlCIOrange = 46
    xlCIBlueGray = 47
    xlCIGray40 = 48
    xlCIDarkTeal = 49
    xlCISeaGreen = 50
    xlCIDarkGreen = 51
    xlCIBrown = 53
    xlCIIndigo = 55
    xlCIGray80 = 56
End Enum