Conditional format based on 2 columns

[WAAZUPEE]

Can anyone help me come up with a conditional formula for what I am trying to accomplish?

I want to search a column (E) to find a value of a cell (E10) I am currently using this formula to do that:

=NOT(ISERROR(MATCH(E10,$E$2:$E$9,0)))

But I would also like to search column (F) to find a value of a cell (F10)

If both cells E10 & F10 find its value in each of these columns AND they are in the same row then condition format the cells


To help explain this I will tell you what I am trying to accomplish, I have a spreadsheet with dies we use, with these dies they have a size across and size around, whenever I enter a new die that needs to be order I enter both of those values (across & around) - I would like basically a warning saying I have already ordered a die with those sizes.

I have attach a spreadsheet sample to help explain

Thanks for you help,

Mike

[6StringJazzer]

I replaced your two rules with a single rule that applies to E10:F10, since the same condition applies to both:

Please Login or Register  to view this content.

[63falcondude]

Highlight columns E and F > Conditional Formatting > New Rule > Use a formula

=COUNTIFS($E$1:$E1,$E1,$F$1:$F1,$F1)>1

Format: Fill Color of your choice > OK > OK

[6StringJazzer]

Highlight columns E and F > Conditional Formatting > New Rule > Use a formula

=COUNTIFS($E$1:$E1,$E1,$F$1:$F1,$F1)>1

Format: Fill Color of your choice > OK > OK

I believe this will be true when both values are found, but does not take into account the requirement that they be in the same row.

[63falcondude]

but does not take into account the requirement that they be in the same row.

I disagree.

With multiple criteria, COUNTIFS works like an AND statement where the matches have to be in the same row.

i.e. Count the number of instances where the value in E10 is found in E2:E10 AND where the value in F10 is found in F2:F10 (of the same row).

Try it out!

[63falcondude]

Also, the CF formula (from post #3) was created under the assumption that the OP will be adding to the data.

If a row 11 was to be added, I assume that the OP wanted the Conditional Formatting to then check rows 2 through 10 and be applied to E11 and F11 if applicable.

[6StringJazzer]

Oh, absolutely right, my apologies.

[63falcondude]

No worries, let's see if it works alright for the OP.

Thanks for the rep!

[WAAZUPEE]

63falcondude your formula worked very well with one exception, if I have text in some of the cells and not numbers will it not work?

A few of my cells in column "E" say "gears" or "backer blade", etc.

Thanks,

Mike

[63falcondude]

It shouldn't matter if the cells are numbers, text, or a mix of both.

Are you saying that the Conditional Formatting formula from post #3 doesn't work as expected somewhere?

[WAAZUPEE]

Correct, I am going to attach the full spreadsheet

If you look at the first tab "die" the formula works great

Take a look at the second tab "full sheet" I cannot seem to get it to work there unless I am doing something wrong

Thanks for all your help!

Mike

[WAAZUPEE]

I am sorry 6StringJazzer formula is the one that has worked for me - 63falcondude I cannot seem to get yours to work at all, see spreadsheet attached above.

Thanks!

Mike

[WAAZUPEE]

Re: Conditional format based on 2 columns
Also, the CF formula (from post #3) was created under the assumption that the OP will be adding to the data.

If a row 11 was to be added, I assume that the OP wanted the Conditional Formatting to then check rows 2 through 10 and be applied to E11 and F11 if applicable.

You are correct I will be adding data to F11 ..... F12 .... Etc.

Thanks!

Mike

[63falcondude]

To implement the CF formula from post #3 to the FULL SHEET, first clear any existing Conditional Formatting.

Highlight columns F and G (the entire columns) > Conditional Formatting > New Rule > Use a formula

=COUNTIFS($F$1:$F1,$F1,$G$1:$G1,$G1)>1

Format: Fill color of your choice > OK > OK

[WAAZUPEE]

Also, the CF formula (from post #3) was created under the assumption that the OP will be adding to the data.

If a row 11 was to be added, I assume that the OP wanted the Conditional Formatting to then check rows 2 through 10 and be applied to E11 and F11 if applicable.

You are correct I will be adding data to F11 ..... F12 .... Etc.

Thanks!

Mike

[WAAZUPEE]

To implement the CF formula from post #3 to the FULL SHEET, first clear any existing Conditional Formatting.

Highlight columns F and G (the entire columns) > Conditional Formatting > New Rule > Use a formula

=COUNTIFS($F$1:$F1,$F1,$G$1:$G1,$G1)>1

Format: Fill color of your choice > OK > OK

Works Perfect!!! Thank you so much!

Thanks,

Mike

[63falcondude]

You're welcome. Glad we could help.