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(D180,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.


--
rbmcc
------------------------------------------------------------------------
rbmcc's Profile: http://www.excelforum.com/member.php...o&userid=30602
View this thread: http://www.excelforum.com/showthread...hreadid=530485

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

--
Regards,
Tom Ogilvy


"rbmcc" wrote:


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(D180,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.


--
rbmcc
------------------------------------------------------------------------
rbmcc's Profile: http://www.excelforum.com/member.php...o&userid=30602
View this thread: http://www.excelforum.com/showthread...hreadid=530485

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(A10,COUNTIF($B:$B,A1)0)
should become
=AND(B10,COUNTIF($C:$C,B1)0)
instead it becomes
=AND(B10,COUNTIF($B:$B,B1)0)


--
rbmcc
------------------------------------------------------------------------
rbmcc's Profile: http://www.excelforum.com/member.php...o&userid=30602
View this thread: http://www.excelforum.com/showthread...hreadid=530485

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

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

--
Regards,
Tom Ogilvy


"rbmcc" wrote:


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(A10,COUNTIF($B:$B,A1)0)
should become
=AND(B10,COUNTIF($C:$C,B1)0)
instead it becomes
=AND(B10,COUNTIF($B:$B,B1)0)


--
rbmcc
------------------------------------------------------------------------
rbmcc's Profile: http://www.excelforum.com/member.php...o&userid=30602
View this thread: http://www.excelforum.com/showthread...hreadid=530485

Ah! thanks for assisting my n00bness :)


--
rbmcc
------------------------------------------------------------------------
rbmcc's Profile: http://www.excelforum.com/member.php...o&userid=30602
View this thread: http://www.excelforum.com/showthread...hreadid=530485