BACKGROUND COLOR CHANGE COMPARING RANGE OF CELLS ON DIFFERENT WORKSHEETS

[jwest63]

Hello all

Thanks in advance for your assistance!

I have a workbook with 4 worksheets. The worksheets are:

• Unexecuted
• JW
• MT
• AR

One worksheet “Unexecuted” is a list of orders awaiting processing. The others are named after employee initials JW, MT, and AR. Each employee worksheet contains catalog numbers assigned to that employee for processing, and each employee worksheet can contain about 60 unique catalog numbers.

The Unexecuted worksheet comes from a report generated in another application and imported into excel. On the Unexecuted sheet, I want to change the background color of cells in column A based on which employee they are assigned to from the employee worksheets..

Contents in column A of their respective worksheets is below. When any of these catalog numbers appear in column A of the Unexecuted worksheet, I want the cell background color, on the Unexecuted sheet, to change. JW-Blue, MT-Pink, AR-Green or whatever colors I ultimately decide on.

JW worksheet​	MT worksheet​	AR worksheet​
BALA00​	BAZA00​	EBDA01​
BANA00​	BBMA00​	EBDA02​
BANB00​	BBMA02​	EBDA05​
BAPA00​	CAAA00​	EBDA06​
BARA00​	CAAA10​	EBDA07​
BARA01​	DBAA06​	EIAA00​
BARA02​	DBAA10​	EIBA00​
BARA03​	DBAA30​	FAZA00​
BARA04​	DBAM00​	FBB900​
BARA05​	DBAN00​	FBDB00​
BARA10​	DBAP01​	FBJA00​
BAZA00​	DBAQ01​	FBNA01​

 

[awoohaw]

try this:

Columns B,C,D are me just figuring out the CF formula.

 

[etaf]

set up 3 rules and use formulas
=COUNTIF(JW!$A$2:$A$100,A2)>0
=COUNTIF(MT!$A$2:$A$100,A2)>0
=COUNTIF(AR!$A$2:$A$100,A2)>0

cond-frmt-sheets-ETAF.xlsx
	A
1	Un
2	BALA00
3	BANA00
4	BANB00
5	BAPA00
6	BARA00
7	BAZA00
8	BBMA00
9	BBMA02
10	CAAA00
11	CAAA10
12	DBAA06
13	DBAA10
14	DBAA30
15	DBAM00
16	EBDA06
17	EBDA07
18	EIAA00
19	EIBA00
20	FAZA00
21	FBB900
22	FBDB00
23	FBJA00
24	FBNA01
25
26
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A100	Expression	=COUNTIF(AR!$A$2:$A$100,A2)>0	text	YES
A2:A100	Expression	=COUNTIF(MT!$A$2:$A$100,A2)>0	text	NO
A2:A100	Expression	=COUNTIF(JW!$A$2:$A$100,A2)>0	text	YES

i have setup a dropbox share with the excel sheet on - will only be on for a few days

cond-frmt-sheets-ETAF.xlsx

Shared with Dropbox

www.dropbox.com