Conditional formatting between two worksheets

[skeller481]

Hi - I have a large spreadsheet with multiple tabs in it. I'm looking for a conditional formatting solution for the following - I'll explain:

Say in my worksheet I have Tab1 and Tab2. Tab1 has a list of customers we work with based on submissions they send in. on Tab2, I keep track of these customers and their submit vs purchased history. However, unless I add the customers manually onto Tab2 they will not be listed. What I would like is for the customers on Tab1 who are not listed in Tab2 to highlight in yellow, preferably using a condition or a formula and not macros.

EX:

[TABLE="width: 289"]
<colgroup><col width="87" style="width:65pt"> <col width="64" style="width:48pt"> <col width="74" style="width:56pt"> <col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 289, colspan: 4"][TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]WORKSHEET[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tab1[/TD]
[TD][/TD]
[TD]Tab2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD][/TD]
[TD]Column A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC Co
[/TD]
[TD][/TD]
[TD]ABC Co[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF Co[/TD]
[TD][/TD]
[TD]GHI Co[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GHI Co[/TD]
[TD][/TD]
[TD]JKL Co[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JKL Co[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
DEF Co would be highlighted in Yellow because it does not show up on Tab2.

 

[yky]

DEF Co would be highlighted in Yellow because it does not show up on Tab2.

Huh? The example shows red.


Enter the following formula in conditional formatting and set the desired format.

=COUNTIF(Tab2!A4:A6,"=" & A4)=0

 

[skeller481]

I couldn't get the text in my example to highlight yellow but that's what I'm looking for

Huh? The example shows red.


Enter the following formula in conditional formatting and set the desired format.

=COUNTIF(Tab2!A4:A6,"=" & A4)=0

 

[skeller481]

Thank you-I'll try that tomorrow

 

[FDibbins]

Don't need the "=" in there, also, lock the ranges...
=COUNTIF(Tab2!$A$4:$A$6,A4)=0

 

[skeller481]

Don't need the "=" in there, also, lock the ranges...
=COUNTIF(Tab2!$A$4:$A$6,A4)=0

Thanks

 

[FDibbins]

Happy to help