No Joy with conditional formatting

[kojak43]

I have a column of dates. I also have a ststus column showing if the job is "C" (completed) or "A" (active). I compare the column of dates to the system date located in cell $J$1. If any of the dates in the column are less than $J$1, the date becomes bold and Red on a yellow field, unless, there is a "C" in the status column.
I wrote a conditional formating formula that said =IF(M6:M100,"C") No change to text.
I added another that read "Cell was less than $J$1" Change text to Bold Red w/ yellow field.
When the system date changes to greater than that cell, ALL changes to Bold Red, including the exempted "C" items.

Any help would be appreciated.

 

[Aladin Akyurek]

On 2002-03-04 06:57, kojak43 wrote:
I have a column of dates. I also have a ststus column showing if the job is "C" (completed) or "A" (active). I compare the column of dates to the system date located in cell $J$1. If any of the dates in the column are less than $J$1, the date becomes bold and Red on a yellow field, unless, there is a "C" in the status column.
I wrote a conditional formating formula that said =IF(M6:M100,"C") No change to text.
I added another that read "Cell was less than $J$1" Change text to Bold Red w/ yellow field.
When the system date changes to greater than that cell, ALL changes to Bold Red, including the exempted "C" items.

Any help would be appreciated.

Trying to figure out of what you've done so far is not easy.

M houses apparently the status values.

I'll just assume that the dates of interest are in L from L6 on.

Select L6:L100.
Activate Format|Conditional Formatting.
Choose Formula Is for Condition 1.
Enter in the formula box:

=AND(L6<$J$1,M6<>"C")

Activate Format.
Set up "bold and Red on a yellow field" as formatting on the Font and Patternss tab.
Click OK, OK.

Hope this is what you want.

 

[lenze]

You need to use two conditional formats in the correct order.
First set a condition that checks the status column for a value of C and apply no formatting. Then set the second condition to check the date against yout system date and format to turn the date Red when desired.

 

[kojak43]

Sorry it was not easy to understand my problem, however, your solution nailed it!

I will have to research what AND means as part of your formula, but it works fine.

Thanks to you both, (I added another condtion where there is no data, the column is blank) I got it where I want it!

Thanks
K