search, compare, and copy color of another cell

no, CF cannot mimic color, but what you might ba able to do is use the same formula from the original CF, to set the color for the 2nd CF?

dit: well, at least in 2007, it cant, i dont have 2010, so i cant say for sure

ok, what defines or creates the colors in table 1? if that is through CF, then could you use those rules for table 2?

bummer

what is the basis of your choice of color? could that be done through CF for table 1?

I have seen this done using VBA, but my VBA sucks I will ask around, so dont give up just yet

One of the first lessons I learned here on the forum, a hard one, too, was that "color is not data". Think about that.

Color is for human reference, it's not a piece analyzable or referencable data. Anytime you are about to manually apply a color to a cell, STOP. Ask yourself why you're doing it and see if you can replace that action with a new piece of REAL data you add to that same row of data. Perhaps a new column that adds a category that instructs that same differentiation you were about to do with a color.

Putting the word "Green" in a new column, that's data. Coloring a cell green for visual reference, that's not data.

So, I will help you with your project but you will need to consider this first. I'm not interested in helping you further develop a bad habit by constructing VBA to try and analyze your manual colorations when you can add actual data instead that would make your further analysis of the original data much simpler and meaningful.

JBeaucaire,

Thank you, I do understand that computers have their limitations, and I can appreciate that.

Thankfully, I woke up with a slight revelation for a VBA-less solution. Maybe you can help me out a little

Since Table 1 only has 6 columns, and each column is assigned a color regardless of what value is in there, I can do something in the vein of:

IF(B4 [[from table 2]] = ANY VALUE IN COLUMN 1 from TABLE 1), then RED
IF(B4 [[from table 2]] = ANY VALUE IN COLUMN 2 from TABLE 1), then GREEN
etc until column 6

and then i may have to do it manually for every cell in table 2, but it's do-able!

if you could advise me whether I'm right // what formula i should be using in the conditional formatting box, i'd appreciate it!

thanks in advance

Ask the VBA forum for advice.
//Ola


To read a cell color:
Insert a separate Row. Then write this formula =CellColor(A2) and copy the formula to the right.
To make the =CellColor(A2) function work: Alt+F11 > Insert Module > Copy and paste the below VBA-code:

Please Login or Register  to view this content.

To fill a cell with a color:
Ask the VBA forum or follow this link.

@ JB i LOVE the explanation about color, very well explained

@ Jona CF cannot, by itself, reference another sheet (well, 2007 cant, anyway). so what you will have to do for that, is on the sheet with table 2, reference those values from table 1. then on table 2, you can use CF to change the color

If you were to give a NAMED RANGE to the column 1 of table 1, perhaps name it CLMN1 and then give then CLMN2 to the second column of table 1, you would then be able to use Conditional Formatting in table2 to color.

=COUNTIF(CLMN1, B4)>0 (color red)

=COUNTIF(CLMN2, B4)>0 (color green)


=====================

If the tables are truly in the same page, then you can use the COUNTIF formulas without named ranges.