Conditional formatting question

paulrus

New Member
Joined
Apr 14, 2002
Messages
15
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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
 
Upvote 0
=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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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