Conditional formatting question

[paulrus]

Hi all,

I am using the following formula for conditional formatting:

=COUNTIF($A$70:$A$84,$E5)

It checks to see if E5 (actually every cell in E) matches A70-84 and if so it changes the format. A70-84 contains a list of state abbreviations & it works great. However, now I want to change the information that the changes the formatting.

Instead of state abbreviations, I want the formatting to change IF there is a DATE being held in the cells in E. It doesn't matter what the date is, just that it is a date. Any other data should be ignored.

Any pointers on this one?

Thanks!

Paul

[Tom Urtis]

OK, here is my shot at this, although I bet someone may suggest a more efficient formula. This worked for me in most cases (see below for details):

=IF(LEN(A70)<5,"",OR(WEEKDAY(A70)=1,WEEKDAY(A70)=2,WEEKDAY(A70)=3,WEEKDAY(A70)=4,WEEKDAY(A70)=5,WEEKDAY(A70)=6,WEEKDAY(A70)=7))

Then copy as needed.

The LEN condition refers to the minimal 5 digit date serial number.

This formula is not perfect, in that if a 6 or 7 digit number is entered, Excel may regard that as a date also and conditionally format the cell, even if your intention for that value is not to have it represent a date, even if you precede it with an apostrophe. It will not format the cell for a value less than 5 characters in length (even if all are digits), nor will it format if an 8-digit value is entered.

Except for those restrictions, does this help? I bet someone will show me where this formula could be improved.

It's do-able with VBA also.

I'd be interested in anyone's suggestion for a tighter conditional format formula solution, but hopefully this may get you started.

Any help?

_________________
Tom Urtis

[ This Message was edited by: Tom Urtis on 2002-05-01 07:09 ]

[Steve Hartman]

It's fairly simple as long as you dates aren't in a custom format.

=AND(LEFT(CELL("format",E1),1)="D",RIGHT(CELL("format",E1),1)*1<6) checks E1 for a standard date format.

[ This Message was edited by: Steve Hartman on 2002-05-01 08:10 ]

[paulrus]

=IF(LEN(A70)<5,"",OR(WEEKDAY(A70)=1,WEEKDAY(A70)=2,WEEKDAY(A70)=3,WEEKDAY(A70)=4,WEEKDAY(A70)=5,WEEKDAY(A70)=6,WEEKDAY(A70)=7))

This works, but only to change the date to a different format, not to convert the entire row to a different format. What happens is Excel automagically changes the row letter so the formula applies to individual cells in a row rather than the entire row - A70, B70, C70, etc. (I'm a newbie, so I'm assuming this is a simple fix)

Paul

[Tom Urtis]

Sorry, I did not gather from your original question that you wanted other cells in the same row formatted, depending on the existence of a date in column A of that given row.

No problem though, just use this modified formula and copy the conditional formatting to the cells of your choice:

=IF(LEN($A70)<5,"",OR(WEEKDAY($A70)=1,WEEKDAY($A70)=2,WEEKDAY($A70)=3,WEEKDAY($A70)=4,WEEKDAY($A70)=5,WEEKDAY($A70)=6,WEEKDAY($A70)=7))

Is that what you want?

[paulrus]

Thanks Tom!

Yes that is exactly what I wanted. So, (warning warning newbie comment) the $ in front of the letter is what keeps it from automatically moving up a letter? My company has Office on every machine but no manual, no training, nothing - I know it a bit, but when it comes to doing things like formatting, etc., I'm lost.

Thanks again!

Paul