I want to use Conditional Formatting. I opt for "formula". The
condition should be that a cell F9 that is formatted to Date 14-Mar-07
(see cell format) need to be filled with a date (any date). If empty
then FALSE.

What is the formulah I need to fill in. DATE(F9<>0) failed for me.

This is the condition that is set for cell H9.

Thank you.

Bart
Excel 2003

=AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"AA Arens" <(E-Mail Removed)> wrote in message
news:100b74f2-d1d6-45e2-aa20-(E-Mail Removed)...
>I want to use Conditional Formatting. I opt for "formula". The
> condition should be that a cell F9 that is formatted to Date 14-Mar-07
> (see cell format) need to be filled with a date (any date). If empty
> then FALSE.
>
> What is the formulah I need to fill in. DATE(F9<>0) failed for me.
>
> This is the condition that is set for cell H9.
>
> Thank you.
>
> Bart
> Excel 2003

On Jan 29, 3:59 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "AA Arens" <bartvandon...@gmail.com> wrote in message
>
> news:100b74f2-d1d6-45e2-aa20-(E-Mail Removed)...
>
> >I want to use Conditional Formatting. I opt for "formula". The
> > condition should be that a cell F9 that is formatted to Date 14-Mar-07
> > (see cell format) need to be filled with a date (any date). If empty
> > then FALSE.
>
> > What is the formulah I need to fill in. DATE(F9<>0) failed for me.
>
> > This is the condition that is set for cell H9.
>
> > Thank you.
>
> > Bart
> > Excel 2003

Hi, this solution did not work for me.


May be I was not so clear and I realised there is another condition.
This is what I would like:

Column:
F G H --> Z
9 (Date) (amount)
10 (Date) (amount)
etc. etc.

Dual conditional formatting:

Cell range G9-20 - Z9-Z20 : Cell fill color become orange if any cell
is filled with amount
Cell range G9-20 - Z9-Z20 : Cell fill color become green if any cell
is filled with amount AND the date column is filled with a date
In other cases cell does not get fill color.

Bart

"AA Arens" <(E-Mail Removed)> wrote in message
news:120a81e5-34a6-4616-9b02-(E-Mail Removed)...
> On Jan 29, 3:59 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)
>>
>> --
>> ---
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>> "AA Arens" <bartvandon...@gmail.com> wrote in message
>>
>> news:100b74f2-d1d6-45e2-aa20-(E-Mail Removed)...
>>
>> >I want to use Conditional Formatting. I opt for "formula". The
>> > condition should be that a cell F9 that is formatted to Date 14-Mar-07
>> > (see cell format) need to be filled with a date (any date). If empty
>> > then FALSE.
>>
>> > What is the formulah I need to fill in. DATE(F9<>0) failed for me.
>>
>> > This is the condition that is set for cell H9.
>>
>> > Thank you.
>>
>> > Bart
>> > Excel 2003
>
> Hi, this solution did not work for me.
>
>
> May be I was not so clear and I realised there is another condition.
> This is what I would like:
>
> Column:
> F G H --> Z
> 9 (Date) (amount)
> 10 (Date) (amount)
> etc. etc.
>
> Dual conditional formatting:
>
> Cell range G9-20 - Z9-Z20 : Cell fill color become orange if any cell
> is filled with amount
> Cell range G9-20 - Z9-Z20 : Cell fill color become green if any cell
> is filled with amount AND the date column is filled with a date
> In other cases cell does not get fill color.
>
> Bart

Condition 2: =ISNUMBER(G9)

Condition 1:
=AND(ISNUMBER(G9),LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)

On Jan 29, 6:31 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> "AAArens" <bartvandon...@gmail.com> wrote in message
>
> news:120a81e5-34a6-4616-9b02-(E-Mail Removed)...
>
>
>
> > On Jan 29, 3:59 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> >> =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)
>
> >> --
> >> ---
> >> HTH
>
> >> Bob
>
> >> (there's no email, no snail mail, but somewhere should be gmail in my
> >> addy)
>
> >> "AAArens" <bartvandon...@gmail.com> wrote in message
>
> >>news:100b74f2-d1d6-45e2-aa20-(E-Mail Removed)...
>
> >> >I want to use Conditional Formatting. I opt for "formula". The
> >> > condition should be that a cell F9 that is formatted to Date 14-Mar-07
> >> > (see cell format) need to be filled with a date (any date). If empty
> >> > then FALSE.
>
> >> > What is the formulah I need to fill in. DATE(F9<>0) failed for me.
>
> >> > This is the condition that is set for cell H9.
>
> >> > Thank you.
>
> >> > Bart
> >> > Excel 2003
>
> > Hi, this solution did not work for me.
>
> > May be I was not so clear and I realised there is another condition.
> > This is what I would like:
>
> > Column:
> > F G H --> Z
> > 9 (Date) (amount)
> > 10 (Date) (amount)
> > etc. etc.
>
> > Dual conditional formatting:
>
> > Cell range G9-20 - Z9-Z20 : Cell fill color become orange if any cell
> > is filled with amount
> > Cell range G9-20 - Z9-Z20 : Cell fill color become green if any cell
> > is filled with amount AND the date column is filled with a date
> > In other cases cell does not get fill color.
>
> > Bart
>
> Condition 2: =ISNUMBER(G9)
>
> Condition 1:
> =AND(ISNUMBER(G9),LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)

Bob, I can't get it working. I filled both conditions in cell G9,
first condition 1 then 2. But only the format I have assigned to
condition 2 is visible if G9 has data, whether F9 is filled in or not.

Bart

It might be because I didn't fix F9 down using absolute referencing.

I have posted an example at http://cjoint.com/?bDnSVGsfR1

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"AA Arens" <(E-Mail Removed)> wrote in message
news:c359fec3-7f58-4528-ac7c-(E-Mail Removed)...
> On Jan 29, 6:31 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> "AAArens" <bartvandon...@gmail.com> wrote in message
>>
>> news:120a81e5-34a6-4616-9b02-(E-Mail Removed)...
>>
>>
>>
>> > On Jan 29, 3:59 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> >> =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)
>>
>> >> --
>> >> ---
>> >> HTH
>>
>> >> Bob
>>
>> >> (there's no email, no snail mail, but somewhere should be gmail in my
>> >> addy)
>>
>> >> "AAArens" <bartvandon...@gmail.com> wrote in message
>>
>> >>news:100b74f2-d1d6-45e2-aa20-(E-Mail Removed)...
>>
>> >> >I want to use Conditional Formatting. I opt for "formula". The
>> >> > condition should be that a cell F9 that is formatted to Date
>> >> > 14-Mar-07
>> >> > (see cell format) need to be filled with a date (any date). If empty
>> >> > then FALSE.
>>
>> >> > What is the formulah I need to fill in. DATE(F9<>0) failed for me.
>>
>> >> > This is the condition that is set for cell H9.
>>
>> >> > Thank you.
>>
>> >> > Bart
>> >> > Excel 2003
>>
>> > Hi, this solution did not work for me.
>>
>> > May be I was not so clear and I realised there is another condition.
>> > This is what I would like:
>>
>> > Column:
>> > F G H --> Z
>> > 9 (Date) (amount)
>> > 10 (Date) (amount)
>> > etc. etc.
>>
>> > Dual conditional formatting:
>>
>> > Cell range G9-20 - Z9-Z20 : Cell fill color become orange if any cell
>> > is filled with amount
>> > Cell range G9-20 - Z9-Z20 : Cell fill color become green if any cell
>> > is filled with amount AND the date column is filled with a date
>> > In other cases cell does not get fill color.
>>
>> > Bart
>>
>> Condition 2: =ISNUMBER(G9)
>>
>> Condition 1:
>> =AND(ISNUMBER(G9),LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)
>
> Bob, I can't get it working. I filled both conditions in cell G9,
> first condition 1 then 2. But only the format I have assigned to
> condition 2 is visible if G9 has data, whether F9 is filled in or not.
>
> Bart

On Jan 29, 7:46 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> It might be because I didn't fix F9 down using absolute referencing.
>
> I have posted an example athttp://cjoint.com/?bDnSVGsfR1
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "AAArens" <bartvandon...@gmail.com> wrote in message
>
> news:c359fec3-7f58-4528-ac7c-(E-Mail Removed)...
>
> > On Jan 29, 6:31 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> >> "AAArens" <bartvandon...@gmail.com> wrote in message
>
> >>news:120a81e5-34a6-4616-9b02-(E-Mail Removed)...
>
> >> > On Jan 29, 3:59 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> >> >> =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)
>
> >> >> --
> >> >> ---
> >> >> HTH
>
> >> >> Bob
>
> >> >> (there's no email, no snail mail, but somewhere should be gmail in my
> >> >> addy)
>
> >> >> "AAArens" <bartvandon...@gmail.com> wrote in message
>
> >> >>news:100b74f2-d1d6-45e2-aa20-(E-Mail Removed)...
>
> >> >> >I want to use Conditional Formatting. I opt for "formula". The
> >> >> > condition should be that a cell F9 that is formatted to Date
> >> >> > 14-Mar-07
> >> >> > (see cell format) need to be filled with a date (any date). If empty
> >> >> > then FALSE.
>
> >> >> > What is the formulah I need to fill in. DATE(F9<>0) failed for me.
>
> >> >> > This is the condition that is set for cell H9.
>
> >> >> > Thank you.
>
> >> >> > Bart
> >> >> > Excel 2003
>
> >> > Hi, this solution did not work for me.
>
> >> > May be I was not so clear and I realised there is another condition.
> >> > This is what I would like:
>
> >> > Column:
> >> > F G H --> Z
> >> > 9 (Date) (amount)
> >> > 10 (Date) (amount)
> >> > etc. etc.
>
> >> > Dual conditional formatting:
>
> >> > Cell range G9-20 - Z9-Z20 : Cell fill color become orange if any cell
> >> > is filled with amount
> >> > Cell range G9-20 - Z9-Z20 : Cell fill color become green if any cell
> >> > is filled with amount AND the date column is filled with a date
> >> > In other cases cell does not get fill color.
>
> >> > Bart
>
> >> Condition 2: =ISNUMBER(G9)
>
> >> Condition 1:
> >> =AND(ISNUMBER(G9),LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)
>
> > Bob, I can't get it working. I filled both conditions in cell G9,
> > first condition 1 then 2. But only the format I have assigned to
> > condition 2 is visible if G9 has data, whether F9 is filled in or not.
>
> > Bart

Hi Bob, it works fine, triggy idea. I also hided the second date
column. As the cells right of the date need to chege colors, I changed
$F$9 into $F9.

Just a small add. I also have a combo column left of the date column
where I can choose the currency either USD, EUR, or IDR. How to have
the cells that are filled in the G-Z area also have italics in case I
choose EUR. In fact there should be a third condition beside the one
we discussed before. What is this condition?




Bart

Bart,

Is that 4 conditions?

Cell range G9-20 - Z9-Z20 : Cell fill colour become orange and amount is
italicised if any cell is filled with amount AND currency is EUR
Cell range G9-20 - Z9-Z20 : Cell fill colour become green and amount is
italicised if any cell is filled with amount AND the date column is filled
with a date AND currency is EUR

Cell range G9-20 - Z9-Z20 : Cell fill colour become orange and amount is not
italicised if any cell is filled with amount AND currency is not EUR
Cell range G9-20 - Z9-Z20 : Cell fill colour become green and amount is not
italicised if any cell is filled with amount AND the date column is filled
with a date AND currency is not EUR
In other cases cell does not get fill colour.

If so, that becomes tricky as CF only as 3 conditions.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"AA Arens" <(E-Mail Removed)> wrote in message
news:c6982c30-8127-4e8a-8b69-(E-Mail Removed)...
> On Jan 29, 7:46 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> It might be because I didn't fix F9 down using absolute referencing.
>>
>> I have posted an example athttp://cjoint.com/?bDnSVGsfR1
>>
>> --
>> ---
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>> "AAArens" <bartvandon...@gmail.com> wrote in message
>>
>> news:c359fec3-7f58-4528-ac7c-(E-Mail Removed)...
>>
>> > On Jan 29, 6:31 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> >> "AAArens" <bartvandon...@gmail.com> wrote in message
>>
>> >>news:120a81e5-34a6-4616-9b02-(E-Mail Removed)...
>>
>> >> > On Jan 29, 3:59 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> >> >> =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)
>>
>> >> >> --
>> >> >> ---
>> >> >> HTH
>>
>> >> >> Bob
>>
>> >> >> (there's no email, no snail mail, but somewhere should be gmail in
>> >> >> my
>> >> >> addy)
>>
>> >> >> "AAArens" <bartvandon...@gmail.com> wrote in message
>>
>> >> >>news:100b74f2-d1d6-45e2-aa20-(E-Mail Removed)...
>>
>> >> >> >I want to use Conditional Formatting. I opt for "formula". The
>> >> >> > condition should be that a cell F9 that is formatted to Date
>> >> >> > 14-Mar-07
>> >> >> > (see cell format) need to be filled with a date (any date). If
>> >> >> > empty
>> >> >> > then FALSE.
>>
>> >> >> > What is the formulah I need to fill in. DATE(F9<>0) failed for
>> >> >> > me.
>>
>> >> >> > This is the condition that is set for cell H9.
>>
>> >> >> > Thank you.
>>
>> >> >> > Bart
>> >> >> > Excel 2003
>>
>> >> > Hi, this solution did not work for me.
>>
>> >> > May be I was not so clear and I realised there is another condition.
>> >> > This is what I would like:
>>
>> >> > Column:
>> >> > F G H --> Z
>> >> > 9 (Date) (amount)
>> >> > 10 (Date) (amount)
>> >> > etc. etc.
>>
>> >> > Dual conditional formatting:
>>
>> >> > Cell range G9-20 - Z9-Z20 : Cell fill color become orange if any
>> >> > cell
>> >> > is filled with amount
>> >> > Cell range G9-20 - Z9-Z20 : Cell fill color become green if any cell
>> >> > is filled with amount AND the date column is filled with a date
>> >> > In other cases cell does not get fill color.
>>
>> >> > Bart
>>
>> >> Condition 2: =ISNUMBER(G9)
>>
>> >> Condition 1:
>> >> =AND(ISNUMBER(G9),LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)
>>
>> > Bob, I can't get it working. I filled both conditions in cell G9,
>> > first condition 1 then 2. But only the format I have assigned to
>> > condition 2 is visible if G9 has data, whether F9 is filled in or not.
>>
>> > Bart
>
> Hi Bob, it works fine, triggy idea. I also hided the second date
> column. As the cells right of the date need to chege colors, I changed
> $F$9 into $F9.
>
> Just a small add. I also have a combo column left of the date column
> where I can choose the currency either USD, EUR, or IDR. How to have
> the cells that are filled in the G-Z area also have italics in case I
> choose EUR. In fact there should be a third condition beside the one
> we discussed before. What is this condition?
>
>
>
>
> Bart

On Jan 30, 6:01 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> Bart,
>
> Is that 4 conditions?
>
> Cell range G9-20 - Z9-Z20 : Cell fill colour become orange and amount is
> italicised if any cell is filled with amount AND currency is EUR
> Cell range G9-20 - Z9-Z20 : Cell fill colour become green and amount is
> italicised if any cell is filled with amount AND the date column is filled
> with a date AND currency is EUR
>
> Cell range G9-20 - Z9-Z20 : Cell fill colour become orange and amount is not
> italicised if any cell is filled with amount AND currency is not EUR
> Cell range G9-20 - Z9-Z20 : Cell fill colour become green and amount is not
> italicised if any cell is filled with amount AND the date column is filled
> with a date AND currency is not EUR
> In other cases cell does not get fill colour.
>
> If so, that becomes tricky as CF only as 3 conditions.
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "AA Arens" <bartvandon...@gmail.com> wrote in message
>
> news:c6982c30-8127-4e8a-8b69-(E-Mail Removed)...
>
> > On Jan 29, 7:46 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> >> It might be because I didn't fix F9 down using absolute referencing.
>
> >> I have posted an example athttp://cjoint.com/?bDnSVGsfR1
>
> >> --
> >> ---
> >> HTH
>
> >> Bob
>
> >> (there's no email, no snail mail, but somewhere should be gmail in my
> >> addy)
>
> >> "AAArens" <bartvandon...@gmail.com> wrote in message
>
> >>news:c359fec3-7f58-4528-ac7c-(E-Mail Removed)...
>
> >> > On Jan 29, 6:31 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> >> >> "AAArens" <bartvandon...@gmail.com> wrote in message
>
> >> >>news:120a81e5-34a6-4616-9b02-(E-Mail Removed)...
>
> >> >> > On Jan 29, 3:59 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> >> >> >> =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)
>
> >> >> >> --
> >> >> >> ---
> >> >> >> HTH
>
> >> >> >> Bob
>
> >> >> >> (there's no email, no snail mail, but somewhere should be gmail in
> >> >> >> my
> >> >> >> addy)
>
> >> >> >> "AAArens" <bartvandon...@gmail.com> wrote in message
>
> >> >> >>news:100b74f2-d1d6-45e2-aa20-(E-Mail Removed)...
>
> >> >> >> >I want to use Conditional Formatting. I opt for "formula". The
> >> >> >> > condition should be that a cell F9 that is formatted to Date
> >> >> >> > 14-Mar-07
> >> >> >> > (see cell format) need to be filled with a date (any date). If
> >> >> >> > empty
> >> >> >> > then FALSE.
>
> >> >> >> > What is the formulah I need to fill in. DATE(F9<>0) failed for
> >> >> >> > me.
>
> >> >> >> > This is the condition that is set for cell H9.
>
> >> >> >> > Thank you.
>
> >> >> >> > Bart
> >> >> >> > Excel 2003
>
> >> >> > Hi, this solution did not work for me.
>
> >> >> > May be I was not so clear and I realised there is another condition.
> >> >> > This is what I would like:
>
> >> >> > Column:
> >> >> > F G H --> Z
> >> >> > 9 (Date) (amount)
> >> >> > 10 (Date) (amount)
> >> >> > etc. etc.
>
> >> >> > Dual conditional formatting:
>
> >> >> > Cell range G9-20 - Z9-Z20 : Cell fill color become orange if any
> >> >> > cell
> >> >> > is filled with amount
> >> >> > Cell range G9-20 - Z9-Z20 : Cell fill color become green if any cell
> >> >> > is filled with amount AND the date column is filled with a date
> >> >> > In other cases cell does not get fill color.
>
> >> >> > Bart
>
> >> >> Condition 2: =ISNUMBER(G9)
>
> >> >> Condition 1:
> >> >> =AND(ISNUMBER(G9),LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)
>
> >> > Bob, I can't get it working. I filled both conditions in cell G9,
> >> > first condition 1 then 2. But only the format I have assigned to
> >> > condition 2 is visible if G9 has data, whether F9 is filled in or not.
>
> >> > Bart
>
> > Hi Bob, it works fine, triggy idea. I also hided the second date
> > column. As the cells right of the date need to chege colors, I changed
> > $F$9 into $F9.
>
> > Just a small add. I also have a combo column left of the date column
> > where I can choose the currency either USD, EUR, or IDR. How to have
> > the cells that are filled in the G-Z area also have italics in case I
> > choose EUR. In fact there should be a third condition beside the one
> > we discussed before. What is this condition?
>
> > Bart

Hmm, if an additional format of a cell value should be italics when a
combo value from another column is set to EUR, I think its one extra
condition because the extra format applies independently of the
"orange -" or "green condition".

Bart

Okay, what column is the currency in?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"AA Arens" <(E-Mail Removed)> wrote in message
news:b13193b9-d6d2-4e6b-b781-(E-Mail Removed)...
> On Jan 30, 6:01 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> Bart,
>>
>> Is that 4 conditions?
>>
>> Cell range G9-20 - Z9-Z20 : Cell fill colour become orange and amount is
>> italicised if any cell is filled with amount AND currency is EUR
>> Cell range G9-20 - Z9-Z20 : Cell fill colour become green and amount is
>> italicised if any cell is filled with amount AND the date column is
>> filled
>> with a date AND currency is EUR
>>
>> Cell range G9-20 - Z9-Z20 : Cell fill colour become orange and amount is
>> not
>> italicised if any cell is filled with amount AND currency is not EUR
>> Cell range G9-20 - Z9-Z20 : Cell fill colour become green and amount is
>> not
>> italicised if any cell is filled with amount AND the date column is
>> filled
>> with a date AND currency is not EUR
>> In other cases cell does not get fill colour.
>>
>> If so, that becomes tricky as CF only as 3 conditions.
>>
>> --
>> ---
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>> "AA Arens" <bartvandon...@gmail.com> wrote in message
>>
>> news:c6982c30-8127-4e8a-8b69-(E-Mail Removed)...
>>
>> > On Jan 29, 7:46 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> >> It might be because I didn't fix F9 down using absolute referencing.
>>
>> >> I have posted an example athttp://cjoint.com/?bDnSVGsfR1
>>
>> >> --
>> >> ---
>> >> HTH
>>
>> >> Bob
>>
>> >> (there's no email, no snail mail, but somewhere should be gmail in my
>> >> addy)
>>
>> >> "AAArens" <bartvandon...@gmail.com> wrote in message
>>
>> >>news:c359fec3-7f58-4528-ac7c-(E-Mail Removed)...
>>
>> >> > On Jan 29, 6:31 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> >> >> "AAArens" <bartvandon...@gmail.com> wrote in message
>>
>> >> >>news:120a81e5-34a6-4616-9b02-(E-Mail Removed)...
>>
>> >> >> > On Jan 29, 3:59 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> >> >> >> =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)
>>
>> >> >> >> --
>> >> >> >> ---
>> >> >> >> HTH
>>
>> >> >> >> Bob
>>
>> >> >> >> (there's no email, no snail mail, but somewhere should be gmail
>> >> >> >> in
>> >> >> >> my
>> >> >> >> addy)
>>
>> >> >> >> "AAArens" <bartvandon...@gmail.com> wrote in message
>>
>> >> >> >>news:100b74f2-d1d6-45e2-aa20-(E-Mail Removed)...
>>
>> >> >> >> >I want to use Conditional Formatting. I opt for "formula". The
>> >> >> >> > condition should be that a cell F9 that is formatted to Date
>> >> >> >> > 14-Mar-07
>> >> >> >> > (see cell format) need to be filled with a date (any date). If
>> >> >> >> > empty
>> >> >> >> > then FALSE.
>>
>> >> >> >> > What is the formulah I need to fill in. DATE(F9<>0) failed for
>> >> >> >> > me.
>>
>> >> >> >> > This is the condition that is set for cell H9.
>>
>> >> >> >> > Thank you.
>>
>> >> >> >> > Bart
>> >> >> >> > Excel 2003
>>
>> >> >> > Hi, this solution did not work for me.
>>
>> >> >> > May be I was not so clear and I realised there is another
>> >> >> > condition.
>> >> >> > This is what I would like:
>>
>> >> >> > Column:
>> >> >> > F G H --> Z
>> >> >> > 9 (Date) (amount)
>> >> >> > 10 (Date) (amount)
>> >> >> > etc. etc.
>>
>> >> >> > Dual conditional formatting:
>>
>> >> >> > Cell range G9-20 - Z9-Z20 : Cell fill color become orange if any
>> >> >> > cell
>> >> >> > is filled with amount
>> >> >> > Cell range G9-20 - Z9-Z20 : Cell fill color become green if any
>> >> >> > cell
>> >> >> > is filled with amount AND the date column is filled with a date
>> >> >> > In other cases cell does not get fill color.
>>
>> >> >> > Bart
>>
>> >> >> Condition 2: =ISNUMBER(G9)
>>
>> >> >> Condition 1:
>> >> >> =AND(ISNUMBER(G9),LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)
>>
>> >> > Bob, I can't get it working. I filled both conditions in cell G9,
>> >> > first condition 1 then 2. But only the format I have assigned to
>> >> > condition 2 is visible if G9 has data, whether F9 is filled in or
>> >> > not.
>>
>> >> > Bart
>>
>> > Hi Bob, it works fine, triggy idea. I also hided the second date
>> > column. As the cells right of the date need to chege colors, I changed
>> > $F$9 into $F9.
>>
>> > Just a small add. I also have a combo column left of the date column
>> > where I can choose the currency either USD, EUR, or IDR. How to have
>> > the cells that are filled in the G-Z area also have italics in case I
>> > choose EUR. In fact there should be a third condition beside the one
>> > we discussed before. What is this condition?
>>
>> > Bart
>
> Hmm, if an additional format of a cell value should be italics when a
> combo value from another column is set to EUR, I think its one extra
> condition because the extra format applies independently of the
> "orange -" or "green condition".
>
> Bart