Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. Thanks! -- rmcgal ------------------------------------------------------------------------ rmcgal's Profile: http://www.excelforum.com/member.php...o&userid=26349 View this thread: http://www.excelforum.com/showthread...hreadid=396149 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP & Conditional Formating Help. | Excel Discussion (Misc queries) | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) | |||
conditional formatting conflict? | Excel Worksheet Functions | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |