Conditional Formatting:Date
 

In Excel(2003)...how do you set up conditional formatting to change the
background color when a date is entered into the cell. I don't need to
format it to any particular range of dates, I simply want the color to
change to green when I enter a completion date in the cell. Other than
entering a date, the cell would only contain text abbreviations for
certain actions.

I have tried several different formulas and cannot get any to work.

It seems so basic...that is probably why it is stumping me.
:confused:

Thanks!


--
rmcgal
------------------------------------------------------------------------
rmcgal's Profile: http://www.excelforum.com/member.php...o&userid=26349
View this thread: http://www.excelforum.com/showthread...hreadid=396149

First, you need to understand that to Excel a Date is just a number that
it interprets as a date and, if formatted as such, displays that number
in a Date/Time format.

If you enter the number 1 in a cell and format as date, 1/1/1900 will
be displayed.

Therefore, you are asking for Conditional Formatting to respond to a
Format. It cannot do that.

I see two options:

1] generate some VBA code to read the contents of the cell and the
formatting of the cell and alter the fill color if it is a date.

2] If, as you say, the cell will either be blank, have a text entry or
a date, use CF to test which it is and format accordingly, as such:

Format the cell (A1 in this example) as Date with your default fill
color (no color)

Set CF Condition 1 as:

Formula Is: =$A$1*10 and choose your desired format for a date
entry (Pattern fill color=Green) Click OK, OK.

Now, a text entry in this cell will do nothing, any number entry will
change the fill color to Green.

HTH

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=396149

Thanks for the tip Bruce.

I went with option 2. That works great, since the only numeric entries
in those cells will be dates.

Have a great day! :)


--
rmcgal
------------------------------------------------------------------------
rmcgal's Profile: http://www.excelforum.com/member.php...o&userid=26349
View this thread: http://www.excelforum.com/showthread...hreadid=396149

Glad it worked for you. Thanks for the feedback, it is always
appreciated.

Cheers!

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=396149