What's sintaxe or formula, please

[Guest]

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 A2100,
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 abou
=IF(D2100=B2:B100;IF(B2:B100=D2100;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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Guest]

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]

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)

 

[Guest]

Sorry for my English and your patience.

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

Thanks.
an

 

[Bob Phillips]

But A4=C4 and D4<>B4 as well, so why one red and the other not?

And I still do not know what you want to achieve.

--

HTH

Bob Phillips

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

 

[Guest]

Ok.

I would like:
In an interval + or - 00:05, the texts in D column cannot be equal of the
texts in B column (fixed texts) and change to red colour.
For another words:
It cannot have same texts in B (fixed) and D columns.
Only if intervals times > 00:05
If the times for same text will be > 00:05 or < 00:05, don't have problem.

Yhank you.
an