conditional formatting and VLookup
 

Hi folks,

Quick question...I have a column in a spreadsheet using VLookup to
place "X" in certain cells if a number in a corresponding column are
within a certain range. I would like to break this up to color the X's
in this column based on the value in their corresponding cell (ie, if
the value in the original cell is between 100,000-499,999, the X in
this column should be blue, if the value is between 500,000 and
1,000,000 the X should be pink, etc.).

Here is my code right now:
=IF(ISNA(VLOOKUP($G5&"-"&"INTERNAL CONNECTIONS MNT",'TX
(8)'!$CF:$CG,2,FALSE))," ","X")

The "conditional formatting" option doesn't seem to work because it
doesn't recognize the VLookup value, only the "X" in the cell.
Is there any way to break up this code by value of the VLookup number
to color the X in the new column?

I've been trying to get the simple [COLOR] command to work but I can't
figure out where to place it in order to integrate it with the rest of
this. Any help/advice from you fabulous smart people would be
appreciated.

Best,
Katherine


--
KatherineMolina
------------------------------------------------------------------------
KatherineMolina's Profile: http://www.excelforum.com/member.php...o&userid=36033
View this thread: http://www.excelforum.com/showthread...hreadid=558243

conditional formatting and VLookup
 

Hi Katherine

You can use formulas in conditional formating eg the below tests the values
in A1 but can be used in any cells conditional formating, these also copy
down OK

=AND(A1100000, A1 <500000)

Then set your format colour

then you can add a second test and so on

give this a try

Regards

--
Tony Green


"KatherineMolina" wrote:


Hi folks,

Quick question...I have a column in a spreadsheet using VLookup to
place "X" in certain cells if a number in a corresponding column are
within a certain range. I would like to break this up to color the X's
in this column based on the value in their corresponding cell (ie, if
the value in the original cell is between 100,000-499,999, the X in
this column should be blue, if the value is between 500,000 and
1,000,000 the X should be pink, etc.).

Here is my code right now:
=IF(ISNA(VLOOKUP($G5&"-"&"INTERNAL CONNECTIONS MNT",'TX
(8)'!$CF:$CG,2,FALSE))," ","X")

The "conditional formatting" option doesn't seem to work because it
doesn't recognize the VLookup value, only the "X" in the cell.
Is there any way to break up this code by value of the VLookup number
to color the X in the new column?

I've been trying to get the simple [COLOR] command to work but I can't
figure out where to place it in order to integrate it with the rest of
this. Any help/advice from you fabulous smart people would be
appreciated.

Best,
Katherine


--
KatherineMolina
------------------------------------------------------------------------
KatherineMolina's Profile: http://www.excelforum.com/member.php...o&userid=36033
View this thread: http://www.excelforum.com/showthread...hreadid=558243