Hi!

I have:
Time in A2, text in B2, another time in C2 and another text in D2.
In A and C columns, the times are incremented one minute.
In B column the text is fixed. In C column the text is variable.

In Conditionall Format in D2 cell I use the next formulae:
=IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0)
to write D2 red if condition is true, and work fine, row by row.

Now, I would like change formulae to work with a block, for example A2:D100,
but I don't know the correct sintax.
For another words:

A B C D
2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval
time between A2 and C2 is < 00:05)
3 09:01 Text2 09:01 Text10 (D3 don't red)
4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval
time is < 00:05 between C5 and A5 both with Text1)
5 09:03 Text4 09:03 Text12
6 09:04 Text6 09:04 ...
7 09:05 Text7 09:05
8 09:06 Text8 09:06
9 09:07 Text9 09:07 Text1 (Don't write red because the interval time
is 00:05:00 between C9 and A2 both with Text1)
....

I thought about
=IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF(C2:C100=A2:A100+"00:05:00";1;0) );0)

But don't work.
I woul like any idea.
Thanks in advance.
an

Why is 4 red but 3 isn't?

Do you want them red if all rows match?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
Hi!

I have:
Time in A2, text in B2, another time in C2 and another text in D2.
In A and C columns, the times are incremented one minute.
In B column the text is fixed. In C column the text is variable.

In Conditionall Format in D2 cell I use the next formulae:
=IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0)
to write D2 red if condition is true, and work fine, row by row.

Now, I would like change formulae to work with a block, for example
A2:D100,
but I don't know the correct sintax.
For another words:

A B C D
2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval
time between A2 and C2 is < 00:05)
3 09:01 Text2 09:01 Text10 (D3 don't red)
4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval
time is < 00:05 between C5 and A5 both with Text1)
5 09:03 Text4 09:03 Text12
6 09:04 Text6 09:04 ...
7 09:05 Text7 09:05
8 09:06 Text8 09:06
9 09:07 Text9 09:07 Text1 (Don't write red because the interval
time
is 00:05:00 between C9 and A2 both with Text1)
...

I thought about

=IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF
(C2:C100=A2:A100+"00:05:00";1;0));0)

But don't work.
I woul like any idea.
Thanks in advance.
an

Sorry for my delay.

3 isn't red because D4<B4 (Text11<Text3)
of where, it does not interest the hour.
It only interests. T write red if two equal texts will be in an interval < 5
min

Thanks.an

"Bob Phillips" wrote:

Why is 4 red but 3 isn't?

Do you want them red if all rows match?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
Hi!

I have:
Time in A2, text in B2, another time in C2 and another text in D2.
In A and C columns, the times are incremented one minute.
In B column the text is fixed. In C column the text is variable.

In Conditionall Format in D2 cell I use the next formulae:
=IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0)
to write D2 red if condition is true, and work fine, row by row.

Now, I would like change formulae to work with a block, for example
A2:D100,
but I don't know the correct sintax.
For another words:

A B C D
2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval
time between A2 and C2 is < 00:05)
3 09:01 Text2 09:01 Text10 (D3 don't red)
4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval
time is < 00:05 between C5 and A5 both with Text1)
5 09:03 Text4 09:03 Text12
6 09:04 Text6 09:04 ...
7 09:05 Text7 09:05
8 09:06 Text8 09:06
9 09:07 Text9 09:07 Text1 (Don't write red because the interval
time
is 00:05:00 between C9 and A2 both with Text1)
...

I thought about

=IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF
(C2:C100=A2:A100+"00:05:00";1;0));0)

But don't work.
I woul like any idea.
Thanks in advance.
an

3 isn't red because D4<B4 (Text11<Text3)
of where, it does not interest the hour.
It only interests write red if two equal texts will be in an interval < 5 min

Thanks.
an

"Bob Phillips" wrote:

Why is 4 red but 3 isn't?

Do you want them red if all rows match?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
Hi!

I have:
Time in A2, text in B2, another time in C2 and another text in D2.
In A and C columns, the times are incremented one minute.
In B column the text is fixed. In C column the text is variable.

In Conditionall Format in D2 cell I use the next formulae:
=IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0)
to write D2 red if condition is true, and work fine, row by row.

Now, I would like change formulae to work with a block, for example
A2:D100,
but I don't know the correct sintax.
For another words:

A B C D
2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval
time between A2 and C2 is < 00:05)
3 09:01 Text2 09:01 Text10 (D3 don't red)
4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval
time is < 00:05 between C5 and A5 both with Text1)
5 09:03 Text4 09:03 Text12
6 09:04 Text6 09:04 ...
7 09:05 Text7 09:05
8 09:06 Text8 09:06
9 09:07 Text9 09:07 Text1 (Don't write red because the interval
time
is 00:05:00 between C9 and A2 both with Text1)
...

I thought about

=IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF
(C2:C100=A2:A100+"00:05:00";1;0));0)

But don't work.
I woul like any idea.
Thanks in advance.
an

Sorry, I don't understand. Text11 is not in your example, and you don't
answer whether all cells must be equal in your modified function.

I guess English is not your first language. Is there not an Excel forum in
your native language?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
3 isn't red because D4<B4 (Text11<Text3)
of where, it does not interest the hour.
It only interests write red if two equal texts will be in an interval < 5
min

Thanks.
an

"Bob Phillips" wrote:

Why is 4 red but 3 isn't?

Do you want them red if all rows match?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
Hi!

I have:
Time in A2, text in B2, another time in C2 and another text in D2.
In A and C columns, the times are incremented one minute.
In B column the text is fixed. In C column the text is variable.

In Conditionall Format in D2 cell I use the next formulae:
=IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0)
to write D2 red if condition is true, and work fine, row by row.

Now, I would like change formulae to work with a block, for example
A2:D100,
but I don't know the correct sintax.
For another words:

A B C D
2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the
interval
time between A2 and C2 is < 00:05)
3 09:01 Text2 09:01 Text10 (D3 don't red)
4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the
interval
time is < 00:05 between C5 and A5 both with Text1)
5 09:03 Text4 09:03 Text12
6 09:04 Text6 09:04 ...
7 09:05 Text7 09:05
8 09:06 Text8 09:06
9 09:07 Text9 09:07 Text1 (Don't write red because the
interval
time
is 00:05:00 between C9 and A2 both with Text1)
...

I thought about


=IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF
(C2:C100=A2:A100+"00:05:00";1;0));0)

But don't work.
I woul like any idea.
Thanks in advance.
an

Sorry for my English and your patience.

D3 isn't red because
A3=C3 but D3<B3

Thanks.
an

"Bob Phillips" wrote:

Sorry, I don't understand. Text11 is not in your example, and you don't
answer whether all cells must be equal in your modified function.

I guess English is not your first language. Is there not an Excel forum in
your native language?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
3 isn't red because D4<B4 (Text11<Text3)
of where, it does not interest the hour.
It only interests write red if two equal texts will be in an interval < 5
min

Thanks.
an

"Bob Phillips" wrote:

Why is 4 red but 3 isn't?

Do you want them red if all rows match?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"an" wrote in message
...
Hi!

I have:
Time in A2, text in B2, another time in C2 and another text in D2.
In A and C columns, the times are incremented one minute.
In B column the text is fixed. In C column the text is variable.

In Conditionall Format in D2 cell I use the next formulae:
=IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0)
to write D2 red if condition is true, and work fine, row by row.

Now, I would like change formulae to work with a block, for example
A2:D100,
but I don't know the correct sintax.
For another words:

A B C D
2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the
interval
time between A2 and C2 is < 00:05)
3 09:01 Text2 09:01 Text10 (D3 don't red)
4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the
interval
time is < 00:05 between C5 and A5 both with Text1)
5 09:03 Text4 09:03 Text12
6 09:04 Text6 09:04 ...
7 09:05 Text7 09:05
8 09:06 Text8 09:06
9 09:07 Text9 09:07 Text1 (Don't write red because the
interval
time
is 00:05:00 between C9 and A2 both with Text1)
...

I thought about


=IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF
(C2:C100=A2:A100+"00:05:00";1;0));0)

But don't work.
I woul like any idea.
Thanks in advance.
an