Column based cell shading

[rbmcc]

Right now I'm using a long formula in a conditional format to color
cells based on information in the column prior to the previous column
(coloring cells in column C based on information from column A, for
example).

This is what I'm using to do this:
=AND(D18>0,OR(D18=F1,D18=F2,D18=F3,D18=F4,D18=F5,... and so on
This will color the cell if it is greater than 0 and equal to any cell
in column F.
well, it is supposed to, at least.

However, I can only input so many characters into the string which only
allows me to compare up to around row 30, and my data set is between
50-70 rows.

My next thought would be to use a macro, but I know very little about
them and was wondering if someone could lend me a hand.

 

[Guest]

=AND(D18>0,Countif($F:$F,D18)>0)

 

[rbmcc]

That worked just great! only one problem, when I selected the lower
right corner of the cell to propagate the formating across my sheet,
the range information doesn't update like everything else. i.e.
=AND(A1>0,COUNTIF($B:$B,A1)>0)
should become
=AND(B1>0,COUNTIF($C:$C,B1)>0)
instead it becomes
=AND(B1>0,COUNTIF($B:$B,B1)>0)

 

[Guest]

I was going on what you described. If you want a range address to
propagate, you should remove the $ signs.

=AND(A1>0,COUNTIF(B:B,A1)>0)

 

[rbmcc]

Ah! thanks for assisting my n00bness