conditional fornatting

[rrowdy]

i have conditionally formatted Cell A7 to go red 5 days before and 5 days after today's date. i want to switch the conditioning of in this cell if an 'S' is entered into B7.......how do i do that

[DBY]

Hello
Perhaps the following conditional formatting formula:

=AND(B7<>"S",OR(A7=TODAY()-5,A7=TODAY()+5))

Hope this helps.
DBY

[grizzly6969]

or you could add a condition --- formula is =B7="S" and format colour

[rrowdy]

'hi there

firstly thx very much for giving me a helping hand....however this formula gives me a circular error message. I enjoy working with excel but getting wee bit frustrated.

regards

[DBY]

Hi
Sorry you're getting frustrated. Hang in there! I'm not sure which formula is giving you a circular reference, but I've attached a sample file with an example of my formula and how it works. I hope I've understood what you require.

DBY

[dzaleski1]

I am trying to use the conditional formatting to highlight the smallest and unique number in a column. I have been able to have the lowest number highlighted, but if there are more than one occurrences of the lowest number, it highlights both. For example, in a column with 1, 2, 3, 4, the "1" is highlighted. When I have a column with 1, 2, 3, 1, both the "1's" are highlighted. In my scenario, there is no uniquge lowest number so nothing should be highlighted. Any help would be greatly appreciated! Darrel

[ChemistB]

Let's say your data is in A2:A50

Selecting A2:A50>Conditional formatting>New Rule>Using formula

=AND(A2=MIN(A$2:A$50), COUNTIF(A$2:A$50, A2)<2)
Does that work for you?

[dzaleski1]

Good try but that doesn't work. The "minimum" number could be a varible number. In column A the minimum unique number might be 2 and in column B the minimum unique number might be 4. Darrel

[rrowdy]

Today -5 Today's Date Today + 5
6/06/2012 11/06/2012 16/06/2012




1/06/2012 < If A7 = A2 or C2 and B7 does not contain 'S' then A7 fill colour = Red

Conditional formula:
=AND(B7<>"S",OR(A7=TODAY()-5,A7=TODAY()+5))



Due to Attend Evidence Result Due to Attend Evidence Result
12/06/12 S 12/06/12 S














the due to attend cell will have and advanced date, and will highlight red 5 days before and 5 days after the due date. If the student successfully attended and their evidence was successful a "S" will cancel the red in the due date cell.

I really appreciate your help and i know that in order to get the right answer one must ask the right question.

thanks again for your help

[ChemistB]

Good try but that doesn't work. The "minimum" number could be a varible number. In column A the minimum unique number might be 2 and in column B the minimum unique number might be 4.

My formula
=AND(A2=MIN(A$2:A$50), COUNTIF(A$2:A$50, A2)<2)
does change with each column because the columns are not anchored (have a $ in front of them). Maybe if you upload an example spreadsheet (Go Advanced> Manage Attachments)

[DBY]

Hello
It's getting a little confusing here as dzakeski1 broke forum rule #2 and posted a question in another person's thread and received a reply. But in reply to your last post rrowdy, perhaps I haven't been getting what you require. I thought you wanted the cell highlighted if it was exactly 5 days before or 5 days after today.

Please Login or Register  to view this content.

Do you want the cell highlighted if it is 'between' those dates? If so take a look at my new attachment. This higlights cell A2 if it conatains a date between 5 days less than today, or 5 days greater than today, providing there is no 'S' in cell B2.

Hope this is what you want. If not, post a sample file with some sample data and desired outcome.
DBY

[rrowdy]

Hello
It's getting a little confusing here as dzakeski1 broke forum rule #2 and posted a question in another person's thread and received a reply. But in reply to your last post rrowdy, perhaps I haven't been getting what you require. I thought you wanted the cell highlighted if it was exactly 5 days before or 5 days after today.

Please Login or Register  to view this content.

highlighted if it is 'between' those dates? If so take a look at my new attachment. This higlights cell A2 if it contains a date between 5 days less than today, or 5 days greater than today, providing there is no 'S' in cell B2.

Hope this is what you want. If not, post a sample file with some sample data and desired outcome.
DBY

This is exactly what i want.....thx very much for you patience. i really appreciate your time




Regards

R