Complex conditional formatting (non-vba preferred)

[Pecan]

Hello all,

I'm attempting to apply conditional formatting to a range of cells in a worksheet, and have been unable to determine the correct combination of functions that provides the desired results. Hopefully I’ve got everything covered here, so we don’t have to do a lot of back and forth getting the desired results.

What I have is an EXCEL 2010 worksheet that helps with estimating hours and costs for a project, and below are descriptions of the columns and the parameters for the conditional formatting.

This is a long one folks! If you have any questions, please let me know...

Pam


MAIN PARAMETERS:
Column HD (CTR Code):
- Should always contain an entry.
- Only evaluating for D, DA, A, and Q.
- Paramaters are different for A/Q vs. D/DA.

Column HB (Grand Total [$]):
- Should always contain a formula.
- Macro exists to create FUNCTION [HASFormula()]

Override (i.e., turn off) Conditional Formatting:
Either of the below should work:
- Cell $D$1="X"
- - OR - -
- Two spaces at the end of a line (i.e., a RIGHT($?#, 2)=" " formula) when entered in:
o Column D for rows with a CTR Code "A" or "Q"
o Column E for rows with a CTR Code "D" or "DA"

CTR CODE-SPECIFIC PARAMETERS:
Parameters For Deliverable Items (CTR Code "D" or "DA"):
- Column HD (CTR Code) - Should always contain an entry. Only evaluate rows that contain "D" or "DA"
- Column D (Document Number) *
- Column E (Document Name) * However, sometimes used for heading information all other cells (with the exception of column EK - Total Hours) should be blank. Column EK may or may not contain hours.
- Column F (Drawing Count) **
- Column G (Document Count) **
- Column H (Outside Review) *
- Column HB (Grand Total)

* When all the other columns noted here have an entry, this cell must not be blank.
'** When all the other columns noted here have an entry, column F or G must have an entry in only one of the columns.

Parameters For Approach Items (CTR Code "A" or "Q"):
- Column HD (CTR Code) - Should always contain an entry. Only evaluate rows that contain "A" or "Q"
- Column D:H (Approach Description)
- Column HB - Grand Total


FORMULAS:
I've given up trying to incorporate all the requirements in one conditional formatting and was attempting to develop several formulas. Below is a formula that I created (that works!) to evaluate columns HB and HD and to remove conditional formatting:

'=AND($D$1<>"X", OR(AND(NOT(HASformula($HB9)), OR($HD9="A", $HD9="D", $HD9="DA", $HD9="$", $HD9="Q")), AND(HASformula($HB9), $HD9="")))

The other formula (posted below) is for DELIVERABLES (CTR Code D or DA) that I was working on, but for which I have not had success. I've gone through several iterations over the past several days, and this is the latest formula and worksheet:

Excel Workbook
	A	B	C	D	E	K	L	M	N
1	D	E	F	G	H	HB	HD
2
3		PROJECT				Grand Total	CTR Code	FORMULASD, DA	Desired Result Col
4
5	1	Group 1					TSCIADQ$HOR	FALSE	F
6	Scope						S	FALSE	F
7	Comments						C	FALSE	F
8	Required Information						I	FALSE	F
9	Approach:					$628,000.00	A	FALSE	F
10	Management						A	FALSE	F
11	MEETINGS:						A	FALSE	F
12	Weekly					$259,100.00	A	FALSE	F
13	Other Administration					$272,000.00	A	FALSE	F
14	Travel Expenses					$160,000.00	A	FALSE	F
15						$12,500.00	A	FALSE	T
16							A	FALSE	F
17	Number	Title	Dwgs.(Shts.)	Docs.	Class*(Y/N)		DX	FALSE	F
18	Documents						DH	FALSE	F
19		HEADING TEST:					DA	FALSE	F
20	AAA	Document		1,000	N	$12,500,000.00	DA	TRUE	F
21		HEADING TEST:					DA	TRUE	T
22		Document		1,000	N	$12,500,000.00	DA	TRUE	T
23	AAA			1	N	$12,500.00	DA	TRUE	T
24	AAA	Document			N	$12,500.00	DA	TRUE	T
25	AAA	Document	1	1	N	$12,500.00	DA	TRUE	T
26	AAA	Document		100		$1,250,000.00	DA	TRUE	T
27	AAA	Document		10	N		DA	TRUE	T
28						$12,500.00	DA	TRUE	T
29							DA	TRUE	F
30	Drawings						DH	FALSE	F
31	BBB	Drawing	1	1	N	$12,500.00	D	TRUE	T
32	BBB	Drawing			N	$12,500.00	D	TRUE	T
MrExcel