Reference a cell based on Fill color?
 

Is it possible to check if a cell has a fill color, preferably with an If
statement rather than a macro, but either way...

If not, is it possible to check if a cell has two "/", rather than just one?

Or even worse, how do you take the text string (it was typed in as text):
Sun 01/15/07 (but sometimes Sun 1/22/2007) and convert that to a numerical
date which can then have the month extrapolated?

I thank you in advance.

Arlen

Reference a cell based on Fill color?
 

You could try Cell("color",cell_ref)
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Arlen" wrote:

Is it possible to check if a cell has a fill color, preferably with an If
statement rather than a macro, but either way...

If not, is it possible to check if a cell has two "/", rather than just one?

Or even worse, how do you take the text string (it was typed in as text):
Sun 01/15/07 (but sometimes Sun 1/22/2007) and convert that to a numerical
date which can then have the month extrapolated?

I thank you in advance.

Arlen

Reference a cell based on Fill color?
 

Apparently, that only works for conditional formatting, if cell text is
colored when it's a negative value.



"M Kan" wrote:

You could try Cell("color",cell_ref)
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Arlen" wrote:

Is it possible to check if a cell has a fill color, preferably with an If
statement rather than a macro, but either way...

If not, is it possible to check if a cell has two "/", rather than just one?

Or even worse, how do you take the text string (it was typed in as text):
Sun 01/15/07 (but sometimes Sun 1/22/2007) and convert that to a numerical
date which can then have the month extrapolated?

I thank you in advance.

Arlen

Reference a cell based on Fill color?
 

Is it possible to check if a cell has a fill color

You need VBA code. See this:

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

is it possible to check if a cell has two "/", rather than just one?

As long as the slashes are part of a TEXT string. This formula will return
the count of slashes in cell A1:

=LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))

Note that this won't work on true Excel dates. The slashes you (might) see
in a true date are from formatting and are for display purposes only. They
don't actually exist in the cell even though you do see them. In this case
seeing is not believing! <g

how do you take the text string...Sun 01/15/07
(but sometimes Sun 1/22/2007) and convert that to
a numerical date which can then have the month extrapolated?

If the day is *always* in ddd format:

To extract the month as a text string:

=TEXT(MID(A1,5,20),"mmm") = Jan
=TEXT(MID(A1,5,20),"mmmm") = January

If the day might vary in length:

=TEXT(MID(A1,FIND(" ",A1)+1,20),"mmm")
=TEXT(MID(A1,FIND(" ",A1)+1,20),"mmmm")

If you want the result as a true Excel date:

These should work but I'm more comfortable with the longer formulas below:

=--MID(A1,5,20)
=--MID(A1,FIND(" ",A1)+1,20)

=--TEXT(MID(A1,5,20),"m/d/yyyy")
=--TEXT(MID(A1,FIND(" ",A1)+1,20),"m/d/yyyy")

Format as DATE

--
Biff
Microsoft Excel MVP


"Arlen" wrote in message
...
Is it possible to check if a cell has a fill color, preferably with an If
statement rather than a macro, but either way...

If not, is it possible to check if a cell has two "/", rather than just
one?

Or even worse, how do you take the text string (it was typed in as text):
Sun 01/15/07 (but sometimes Sun 1/22/2007) and convert that to a numerical
date which can then have the month extrapolated?

I thank you in advance.

Arlen