Highlight Values in Range without Loop

[mExd2014]

Hi all

I wonder if anyone can help.

I have some data with set headers. I want to highlight cells greater than zero in a particular column referencing its header (as the column is not always in a fixed position). However I also want to exclude entries if the adjacent column includes a particular reference.

Example - Column I = "Amount", column E = "Comments".

If the amount column has values greater than zero, I want to highlight these cells but only if the comments column does not contain the word 'exclude'.

Now I can do this with a for each loop using the Appliation.WorksheetFunction.Match formula to locate the headers and then highlight the cells however the loop is cumbersome.

Is there a better alternative?

Many thanks for any comments or points in the right direction.

 

[JamesCanale]

I used conditional formatting, where the data is in columns A-O and the comments could be anywhere in there, and the "Amount" could be anywhere in there.

MrExcelPlayground7.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Amount			Comments				Amount
2		2			okay				12
3		-5							15
4		4			okay				-10
5		4			Exclude this				15
6		5							-10
7		5			exclude				30
8		-10							-15
9		5			okay				50
10
11
12
13
14
Sheet14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:O20	Expression	=AND(SUM(IF(ISERR(SEARCH("exclude",LOWER($A2:$O2))),0,1))=0,IF(A$1="Amount",TRUE,FALSE),A2>0)	text	NO

 

[mExd2014]

Problem for me is my headings are not always in the same column to use conditional formatting.

Is there a way via VBA to sort the referenced column and then highlight values above zero I wonder

 

[JamesCanale]

Any column will work. In A-O in my example, but that can be easily changed to A-ZZ or whatever. As long as the header is in Row 1.

 

[mExd2014]

Sorry I should have stated that I want it automated so that my macro is summing the total of the cells whilst simultaneously highlighting the values too. The first part is fine, its just whether I can highlight the cells without looping as there can be quite a few cells in the range.

Appreciate the conditional format option but the problem is my Amount column is not fixed in each worksheet so would need to amend the conditional formatting each time.

 

[JamesCanale]

I have the whole wide range conditionally formatted. But the formula ignores highlighting if the top of the column doesn't say "Amount". You might make a formula that does the conditional totalling too.