Conditional formatting based on formula applied to two columns.

[heretolearnexcel]

Hi,

I have the following data below.

I would like to use a conditional formatting formula to highlight each row when for instance "DILA" in the section "Existencia" is =0, and in "¿Está activa la promoción? 1= sí, 0= no" is =1. And also highlight with a different color when for Dila in the section "Existencia" is >0 and in "¿Está activa la promoción? 1= sí, 0= no" is =0

I came up with the following temprary solution using and AND formulas in the columns: L:P and Q:U, for when the conditions are met. I know the conditional formatting would apply the formatting when ANY of the columns (DILA, Matriz, etc) satisfy the condition, and that my temporary solution gives more detail, but I would still like to highlight the rows when one of the columns satisfies the condition.

Does anyone have any ideas on how to do this?

Thanks in advance, any help is greatly appreciated.

promociones tnj.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		Existencia					¿Está activa la promoción? 1= sí, 0= no					¿Pendiente activar promoción?					¿Pendiente inactivar promoción?
2	Código	Almacén General	Matriz	PLAZA PROGRESO	DILA	PLAZA LUCERO	Almacén General	Matriz	PLAZA PROGRESO	DILA	PLAZA LUCERO	Almacén General	Matriz	PLAZA PROGRESO	DILA	PLAZA LUCERO	Almacén General	Matriz	PLAZA PROGRESO	DILA	PLAZA LUCERO
3	7500464581303	2	17	4	4	6	1	1	1	1	1	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
resumen

Cell Formulas
Range	Formula
B3	B3	=XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!C:C,"not found",0)
C3	C3	=XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!E:E,"not found",0)
D3	D3	=XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!G:G,"not found",0)
E3	E3	=XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!I:I,"not found",0)
F3	F3	=XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!O:O,"not found",0)
G3:K3	G3	=SUMIFS(promociones!$O:$O,promociones!$B:$B,resumen!$A3,promociones!$A:$A,resumen!G$2)
L3:P3	L3	=AND(B3>0,G3=0)
Q3:U3	Q3	=AND(G3=1,B3=0)

Named Ranges
Name	Refers To	Cells
promociones!_FilterDatabase	=promociones!$A$1:$M$363	G3:K3

 

[heretolearnexcel]

Does anyone have any input on this? Maybe something isn't clear enough about my explanation and you'd like me to clarify it?

 

[Joe4]

Quite frankly, I found your description a bit confusing.

Just break the rules down for use simply by telling us what values you are looking for in what columns, exactly (refer to columns by their column letter).
If you can list those details in an easy-to-understand list, I think you may get more responses.

 

[heretolearnexcel]

Quite frankly, I found you description a bit confusing.

Just break the rules down for use simply by telling us what values you are looking for in what columns, exactly (refer to columns by their column letter).
If you can list those details in an easy-to-understand list, I think you may get more responses.

Hey, thanks for the feedback. I will do what you suggested.

 

[heretolearnexcel]

Quite frankly, I found you description a bit confusing.

Just break the rules down for use simply by telling us what values you are looking for in what columns, exactly (refer to columns by their column letter).
If you can list those details in an easy-to-understand list, I think you may get more responses.

So, I summarized it as follows:

When B= 0 AND G =1 highlight B and G (in all rows this happens) yellow

When C= 0 AND H =1 highlight C and D (in whatever row this happens) yellow

When D= 0 AND I =1 highlight D and I (in whatever row this happens) yellow

When E= 0 AND J =1 highlight E and J (in whatever row this happens) yellow

When F= 0 AND K =1 highlight F and K (in whatever row this happens) yellow



When B >0 AND G =0 highlight B and G (in whatever row this happens) blue

When C >0 AND H =0 highlight C and D (in whatever row this happens) blue

When D >0 AND I =0 highlight D and I (in whatever row this happens) blue

When E >0 AND J =0 highlight E and J (in whatever row this happens) blue

When F >0 AND K =0 highlight F and K (in whatever row this happens) blue

 

[Joe4]

You would use a separate Conditional Formatting rule for each one.
So let's look at the first one:

When B= 0 AND G =1 highlight B and G (in all rows this happens) yellow

Since columns B and G are the columns that you want to format, first select BOTH of them (using the CTRL key to select two non-contiguous columns).
Then go to Conditional Formatting, select "New Rule", and choose the "Use a formula to determine which cells to format" option.
Then enter in the following formula:

=AND($B1=0,$G1=1)

and choose your yellow formatting option.

Then, simply repeat this logic for all your other rules.

A few tips:
- Write the formula as it applies to the first cell in your selection. Excel will automatically adjust it for the others.
(Note that we use the "$" in front of the column reference to "lock" that down, but not in front of row reference, so that will change for each row)
- If you find it is too slow, try selecting just the rows you want first instead of formatting the enter column (i.e. select ranges B1:B100 and G1:G100).

Hope that helps.

 

[heretolearnexcel]

You would use a separate Conditional Formatting rule for each one.
So let's look at the first one:


Since columns B and G are the columns that you want to format, first select BOTH of them (using the CTRL key to select two non-contiguous columns).
Then go to Conditional Formatting, select "New Rule", and choose the "Use a formula to determine which cells to format" option.
Then enter in the following formula:

=AND($B1=0,$G1=1)

and choose your yellow formatting option.

Then, simply repeat this logic for all your other rules.

A few tips:
- Write the formula as it applies to the first cell in your selection. Excel will automatically adjust it for the others.
(Note that we use the "$" in front of the column reference to "lock" that down, but not in front of row reference, so that will change for each row)
- If you find it is too slow, try selecting just the rows you want first instead of formatting the enter column (i.e. select ranges B1:B100 and G1:G100).

Hope that helps.

That works great! Thanks for taking the time to help out, I appreciate it.

 

[Joe4]

You are welcome.
Glad I was able to help!