Conditional formatting based on formula applied to two columns.

heretolearnexcel

Board Regular
Joined
Jan 22, 2019
Messages
61
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTU
1Existencia¿Está activa la promoción? 1= sí, 0= no¿Pendiente activar promoción?¿Pendiente inactivar promoción?
2CódigoAlmacén GeneralMatrizPLAZA PROGRESODILAPLAZA LUCEROAlmacén GeneralMatrizPLAZA PROGRESODILAPLAZA LUCEROAlmacén GeneralMatrizPLAZA PROGRESODILAPLAZA LUCEROAlmacén GeneralMatrizPLAZA PROGRESODILAPLAZA LUCERO
3750046458130321744611111FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
resumen
Cell Formulas
RangeFormula
B3B3=XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!C:C,"not found",0)
C3C3=XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!E:E,"not found",0)
D3D3=XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!G:G,"not found",0)
E3E3=XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!I:I,"not found",0)
F3F3=XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!O:O,"not found",0)
G3:K3G3=SUMIFS(promociones!$O:$O,promociones!$B:$B,resumen!$A3,promociones!$A:$A,resumen!G$2)
L3:P3L3=AND(B3>0,G3=0)
Q3:U3Q3=AND(G3=1,B3=0)
Named Ranges
NameRefers ToCells
promociones!_FilterDatabase=promociones!$A$1:$M$363G3:K3
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Does anyone have any input on this? Maybe something isn't clear enough about my explanation and you'd like me to clarify it?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Excel 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.
 
Upvote 0
Solution
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:
Excel 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.
 
Upvote 0
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,223,250
Messages
6,171,036
Members
452,374
Latest member
keccles

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