Conditional Formatting

[marshak]

Hello all!
I am once again needing your assistance. I have the following spreadsheet. Here's what I need to do:

100478__130644324v1_CF Chronology.XLSX
	A	B	C	D	E	F
1	Date	Event	Source	Designation	Primary Relevance	Secondary Relevance
2	1995	Event 1	Source 1		Caputo
3	1994-1997	Event 2	Source 2		Caputo
4	1997-2002	Event 3	Source 3		Caputo
5	June 1997-Dec. 2002	Event 4	Source 4		Caputo
6	2000	Event 5	Source 5		USRC Background	Caputo
7	2002	Event 6	Source 6		Caputo
8	37860	Event 7	Source 7		Dorsey Connections	Caputo
9	39104	Event 8	Source 8		Dorsey Connections
10	39232	Event 9	Source 9		Dorsey Connections
11	39255	Event 10	Source 10		Dorsey Connections
12	39646	Event 11	Source 11		Dorsey Connections
Sheet1

1. When I enter a value into column D, I need that row to change color. For example, if I enter "Confidential" into D10, I need row 10 to change color to "yellow". Any of the following five words will be typed into column D, and I need that row to change color based on the following.

1. Confidential: Yellow
2. AEO: Orange
3. Partially Privileged/Redacted: Red
4. Not Produced/Privileged: Dark Red
5. Not Produced/Not Privileged: Blue

2. I need to take this a step further, if possible. If I enter "Confidential" and "AEO" into D10, I need row 10 to change color to "orange" -- the darker color of the options typed.
Likewise, if I enter "AEO" and "Not Produced/Not Privileged" -- row should color "Blue"

Please let me know if you have any questions. Once again, thank you in advance for your assistance.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is this what you mean?

You need to ensure the rules are in the correct order as shown in the bottom image (Conditional Formatting -> Manage rules) where you can change the order using the arrows shown.

22 07 06.xlsm
	A	B	C	D	E	F
1	Date	Event	Source	Designation	Primary Relevance	Secondary Relevance
2	1995	Event 1	Source 1	Not Produced/Not Privileged	Caputo
3	1994-1997	Event 2	Source 2		Caputo
4	1997-2002	Event 3	Source 3	Confidential Not produced/Privileged AEO	Caputo
5	June 1997-Dec. 2002	Event 4	Source 4	Confidential	Caputo
6	2000	Event 5	Source 5	Not Produced/Not Privileged Confidential	USRC Background	Caputo
7	2002	Event 6	Source 6		Caputo
8	37860	Event 7	Source 7	AEO	Dorsey Connections	Caputo
9	39104	Event 8	Source 8		Dorsey Connections
10	39232	Event 9	Source 9	Confidential AEO	Dorsey Connections
11	39255	Event 10	Source 10		Dorsey Connections
12	39646	Event 11	Source 11	AEO Not produced/Privileged	Dorsey Connections
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F12	Expression	=ISNUMBER(SEARCH("Not Produced/Not Privileged",$D2))	text	NO
A2:F12	Expression	=OR(ISNUMBER(SEARCH("Partially Privileged/Redacted",$D2)),ISNUMBER(SEARCH("Not Produced/Privileged",$D2)))	text	NO
A2:F12	Expression	=ISNUMBER(SEARCH("AEO",$D2))	text	NO
A2:F12	Expression	=ISNUMBER(SEARCH("Confidential",$D2))	text	NO