Conditional Formating Never apply

[Ramadan2512]

I'm getting crazy with conditional formatting as it never apply on my table while I did everything
I have a workbook with many sheets and I want to apply conditional formatting between two sheets as follows:
(if the unit number in sheet1 "C" is found in sheet2 "D" and "K" in sheet2 = "Open" ) then color the entire row in sheet1 in red
sheet1 name"district1" & sheet2 is "stop work"
and i use this formula in conditional formatting (=AND('Stop Work'!$K8="Open",COUNTIF('Stop Work'!$D8:$D351,District1!$C9))
I tried everything but i doesnt work while when i try it on other sheets it works normally and what making me more crazy that it works when the text ="done" not "open" while also i tried it to be number not text and changed the cell formatting to general or text but nothing
my sheet 1 table doesn't has a conflict with red color and sheet2 has a mcro to update it's data from another workbook

please help me what to do
attached 2 screenshots

 

[DanteAmor]

Try this:

=SUMPRODUCT(('stop work'!$D$8:$D$2000=$C9)*('stop work'!$K$8:$K$2000="Open"))

Change the 2000 to the line number up to where you have data on the "stop work" sheet, leaving a gap of lines. For example, if you have 1900 lines then you can put up to 2500.

Applies to:
=$A$9:$Z$1000
The same for the number 1000, it is as far as you want it to apply.

 

[Ramadan2512]

Try this:

=SUMPRODUCT(('stop work'!$D$8:$D$2000=$C9)*('stop work'!$K$8:$K$2000="Open"))

Change the 2000 to the line number up to where you have data on the "stop work" sheet, leaving a gap of lines. For example, if you have 1900 lines then you can put up to 2500.

Applies to:
=$A$9:$Z$1000
The same for the number 1000, it is as far as you want it to apply.

@DanteAmor , Wow you are a genius .... it owrks but let me know just for info why 1000, 2000,2500 not for example 351?

 

[DanteAmor]

it owrks but let me know just for info why 1000, 2000,2500 not for example 351?

If there will always be 351 lines, then that is fine, but if in the future the number of lines will increase, it is advisable to leave some slack in the formula, so that you do not have to adjust the formula every time your data grows.