I use this formula in condition formatting to change the row color red based on matching value between 2 sheets in the same workbook =SUMPRODUCT(('Stop Work'!$D$8:$D$500=$C10)*('Stop Work'!$K$8:$K$500="Open"))
the formula says (if "D" in "stop work" sheet = any cell of "C" in this sheet and "K" in "stop work" sheet = "open" then color the row in red
and because this rule is repeated in many sheets in the same workbook and some cells value can be the same in other sheets so I get wrong result in other sheet that shouldn't be colored so I try to add another condition to make the formula apply in this sheet only (for example if (if "D" in "stop work" sheet = any cell of "C" in this sheet and "K" in "stop work" sheet = "open" and check if the adjacent cell "E10" in stop work sheet = ($A$1) in this sheet) then color the row
I hope I could expalined it well
thank you in advance
I have tried this formula and it works
=SUMPRODUCT(('Stop Work'!$D$8:$D$500=$C10)('Stop Work'!$K$8:$K$500="Open")('Stop Work'!$C$8:$C$500=$B$2))
thank you anyway