RE:conditionally-highlight-cells-in-one-column-based-on-text-values-in-another-column

[protonLeah]

I have a question regarding this thread.
http://www.excelforum.com/excel-prog...er-column.html
When the formula"

Please Login or Register  to view this content.

is pasted into various columns in Doug's worksheet, it gives the correct true/false values but when inserted into conditional formatting, it does not apply any format. Does anyone know why?

[daddylonglegs]

Not quite sure why it doesn't work.....but I don't think it needs to be that complex.

Rather than using COLUMN() and ROW() the standard practice would be to just refer to cells specifically, e.g. no need for the test COLUMN()=MATCH("Paper",A$1:Z$1,0), assuming there is only one column with the header "Paper" which is the case here, I think. Then, assuming the conditional formatting is in, say V4, you can just do a direct comparison to the cell in question, i.e. V$1="Paper".

Also you don't need to use INDIRECT(ADDRESS type constructions, INDEX is better.

In view of the above you could apply conditional formatting to the whole range beginning at B2 with this formula

=AND(A$1="Paper",OR(INDEX($A2:$Z2,MATCH("writing tool",$A$1:$Z$1,0))="Pen",INDEX($A2:$Z2,MATCH("writing tool",$A$1:$Z$1,0))="Pencil"))

which works for me.......