Formula to test for cell color?

Houstonwolf

Houstonwolf

Board Regular
Joined
Jul 28, 2006
Messages
154
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
CDEFGHIJ
1Container#ArrivalatPortofDischarge(Berth)TerminalDepartureRailDepartureRailArrivalatMemphisExpectedDeliveryDueDateIPN/CMR/OCMRAppt.Date
2CAXU81135908/29xxxD750
3FCIU83101108/29xxxD750
4FSCU98243718/29xxxD750
5HJCU19100408/29xxxD750
6HJCU14060659/3xxxD750
7HJCU15325679/3xxxD750
8CAIU24215729/3xxxD750
9GLDU75847209/3xxxD750
Memphis, TN.


Thanks, everyone!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
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
 
Upvote 0
Oaktree said:
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
Click to expand...

Works perfectly! What exactly have I just done? What does the '4' refer to?
 
Upvote 0
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
 
Upvote 0
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.

Code: 
'=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

Code: 
'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
 
Upvote 0
Oaktree said:
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
Click to expand...

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...
 
Upvote 0
Thanks, Kenneth. This might be a little too extensive for me right now, but I look forward to trying it someday.

Kenneth Hobson said:
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.

Code: 
'=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

Code: 
'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
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
should this formula work
Replies
3
Views
165
awoohaw
awoohaw
S
  • Solved
Conditional format dates in a range which are x days from today
Replies
3
Views
488
Sallyprit
S
N
  • Question
Adding a formula to a cell if the cell is a color
Replies
7
Views
148
Cubist
Cubist
M
  • Question
how to make highlights dependent on distance to today's date?
Replies
5
Views
685
etaf
etaf
S
  • Question
IF cells in column A contains a dates that are 7 days before todays date then output the date and the value in the coulumn next to it in a list
Replies
1
Views
407
etaf
etaf

Forum statistics

Threads
1,215,373
Messages
6,124,544
Members
449,169
Latest member
mm424

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top