Global Conditional Formatting

[stereosympathy]

Below is a piece of the worksheet I am currently using. I am using conditional formatting to change colors based on column I. If "approved" then green, if "Awaiting App" Then red. Now when I am done with each line I have a macro that takes it off this list. The problem is I have to keep setting up conditional formatting every time a line is knocked off, I am wondering if there is a way that I can apply global formatting based on what is typed into column I. So every cell is controlled by one format setting. Any help is much appreciated. Thanks.

Unique Print Schedule (version 1).xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
4	PO	DATE				SHIP		SHIP			TOWEL
5	NUM	REC'D	CUSTOMER	PRINTNAME	Screens	DATE	QTY	Type	PPAPP	STKAPP	Model	Handling	Complete
6						PrintTotal:	223869		CurrentMonthPrintTotal			210669
7	15797-A	8/8/2007	Joseph's	Joseph'sSports	4	8/14/2007	500	UPSGND	Approved	None	1117-10WHHIT	None
8	15427	8/6/2007	Agua	ModernMedicalSystems	1	8/20/2007	100	UPSGND	Approved	None	1626-30WHDC	cornerhookandgmt
9	15814	8/9/2007	Agua	InterstateWorldwideLogo	2	8/16/2007	200	UPSGND	Approved	None	1625-25WHHC	Grommet&Hook
10	15816	8/9/2007	CustomTowels	ChineseChristianChurch	1	8/16/2007	60	UPSGND	Approved	None	1118-16WHDC	None
11	15794	8/8/2007	Agua	RickMckeon10K	1	8/17/2007	101	UPSGND	Approved	None	1625-25WHHC	None
12	15834	8/10/2007	BaldwinMotorsports	BaldwinMotorsports	3	8/17/2007	100	UPSGND	AwaitingApp	None	1144-35WHHC	DoubleStrike/shipASAP
13	15788	8/8/2007	Agua	RutgersR&SongLyrics		8/20/2007	7512	GTS	Approved	StrikeNeeded	1518-15WHHL	Ship12extrastoAgua
14	15686	8/1/2007	Agua	CharlestonBeachMusic	1	8/20/2007	250	UPSGND	Approved	None	1218-13WHFI	None
15	15705	7/20/2007	Agua	JamesEKilleen	1	8/27/2007	144	UPSGND	Approved	None	1626-35WHDA-LC	Silverhookandgmt
16	15386	8/3/2007	LemiuxGroupLP	KidsClub	6	8/27/2007	2500	bestway	Approved	None	1118-12WHHR	None
17	15778	8/7/2007	PPISports	U.S.Cellular/Wisconsin"W"	3	8/28/2007	55200	ADVISE	Approved	None	1118-12WHHR	usesecondsforextra's
18	15750	8/6/2007	GreenBayPackers	ForceProud2BLoud	3	8/29/2007	144,002	UPSGND	Approved	strikesent	1118-12WHHR	None
19	14085	8/1/2007	McArthur	PittsburghPanthersPitt	2	OnHold	11,200	Best	Approved	StrikeSent	S1118-12WHHR	None
20	15749	7/18/2007	Agua	FireAntz	3	PENDING	2000	UPSGND	AwaitingApp	None	1118-12WHHR	None
UpComingOrders

 

[stereosympathy]

Wow, no replies... This must be a really stupid question.

 

[Scott Huish]

If a row is deleted, what exactly are you looking to conditionally format?

 

[stereosympathy]

The data in a row is deleted, everything under it moves up, and the row that was formatted is now not formatted. There must be a macro that could do what i'm looking for. The formatting needs to remain in the row for the next set of data I enter.

 

[Scott Huish]

Select rows the range A7:M65536 (or as far down as you would ever possibly go)

Conditional Formatting:
Formula 1:
=$I7="Approved"
Format as desired

Formula 2:
=$I7="Awaiting App"
Format as desired

Not sure what the yellow is based on.

 

[dave3009]

It may have something to do with the Absolute referencing "$"

They can be a real bugger if the entire row isn't deleted

 

[stereosympathy]

Thanks a lot HOTPEPPER! I didn't know that I could highlight all cells and it would apply the formatting all the way down the sheet. I really appreciate the help!