Conditional Formating Never apply

Ramadan

Board Regular
Joined
Jan 20, 2024
Messages
113
Office Version
  1. 2021
Platform
  1. Windows
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
Sheet1.png
Sheet2.png
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Try this:

Excel Formula:
=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.
 
Upvote 0
Solution
@DanteAmor , Wow you are a genius .... it owrks but let me know just for info why 1000, 2000,2500 not for example 351?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,462
Messages
6,191,177
Members
453,644
Latest member
karlpravin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top