Conditional Format based on 2nd cell

[Excelrookie86]

So to piggy back off of this, any way to formulate for the following?

If Column J is <today() and Column M is <100 format the cell in J as red

I tried =AND($J:$J>TODAY(), $M:$M<100%)

But it isn't picking up the fact that m13 is 0 and therefore j13 should be red.
@DanteAmor

 

[DanteAmor]

You must put the following formula in the conditional format.

- In the New Formatting Rule window, select Use a formula to determine which cells to format.
- Enter the formula in the corresponding box.

Try:

varios 28jun2023.xlsm
	J	K	L	M
10
11	27-jun			99%
12	28-jun			99%
13	29-jun			99%
14	27-jun			100%
15	28-jun			100%
16	29-jun			100%
Hoja6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J11:J1000	Expression	=AND($J11<TODAY(),$M11<100%,$J11<>"")	text	NO

 

[Excelrookie86]

T

You must put the following formula in the conditional format.

- In the New Formatting Rule window, select Use a formula to determine which cells to format.
- Enter the formula in the corresponding box.

Try:

varios 28jun2023.xlsm
	J	K	L	M
10
11	27-jun			99%
12	28-jun			99%
13	29-jun			99%
14	27-jun			100%
15	28-jun			100%
16	29-jun			100%
Hoja6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J11:J1000	Expression	=AND($J11<TODAY(),$M11<100%,$J11<>"")	text	NO

Thank you.
Getting closer but now ALL cells are red, regardless of the % in the M cell

 

[DanteAmor]

You must put the following formula in the conditional format.

- In the New Formatting Rule window, select Use a formula to determine which cells to format.
- Enter the formula in the corresponding box.

=AND($J11<TODAY(),$M11<100%,$J11<>"")

 

[Excelrookie86]

You must put the following formula in the conditional format.

- In the New Formatting Rule window, select Use a formula to determine which cells to format.
- Enter the formula in the corresponding box.


View attachment 94394

=AND($J11<TODAY(),$M11<100%,$J11<>"")

Ive made the changes but no go, see M8 as example. 55% due 6/23 but not red.
If it helps there is also one red that shouldnt be. M24

 

[DanteAmor]

What do you have in Applies to:

What do you have in cell M11

 

[Excelrookie86]

What do you have in Applies to:

View attachment 94399​

What do you have in cell M11

View attachment 94400​

hmm changed it to that and same outcome
I had it to $j$:$j$ but changed to yours and same issue

 

[DanteAmor]

The formula was changed.
You should also start on row 11.

And check that in Applies to, it also starts at 11.

 

[DanteAmor]

but changed to yours and same issue

It is not the same issue, from post #2 I told you that you should put the formula
And in the example everything starts in row 11.

Please pay more attention to the examples, that way you would have already solved it from the beginning.

Do not despair, you will see that everything will be fine.

 

[Excelrookie86]

It is not the same issue, from post #2 I told you that you should put the formula
And in the example everything starts in row 11.

Please pay more attention to the examples, that way you would have already solved it from the beginning.

Do not despair, you will see that everything will be fine.



View attachment 94404

Thank you, I have resolved this. I appreciate your patience. I am working from a work PC and can not upload the file itself to make it easier on you.

I GREATLY appreciate your time and effort. Thank you