conidtional formatting across columns

[wakeandbake]

I have several times to get this work but no dice. So, here it goes:

I have three columns of data. A column of keys that refer to an object, a columns of objects, and an amount. (The examples is below). I also have a combo box that list all of the keys in column A. Currently, I have the combo box set to populate a single cell with any key that is selected. So, If I scroll down and select e33e, the same key appears in cell F5.

I am trying to create a conditional formatting that highlights all the cells with the same key. For instance, say I select e33e from the combo box, then A1,B1C1 and A5,B5,C5 all become a specific color, say blue. Or if I select j66j A4,B4,C4 and A6,B6,C6 all turn red.


I would select A1, go to conditional formatting, new rule, use forumla, and type in:

=IF(AND($A$1="e33e", $F$5="e33e"), TRUE, FALSE)


The f5 remember because that is where the combo box populated the word that is selected. So this works and both A3 and F5 turn green whenever I select it on the combo box.

However, there are two things I cant do properly. I want to set it so that it picks up ALL the keys in column a that have "e33e" (A1 and A5 in the example below) and I want to to highlight the corresponding rows that include the object and amount (so A1,B1,C1 and A5,B5,and C5).

I have tried several times to in the rules manager to apply the formatting, but it always ends up highlighting the entire column, and not just the specific keys

Any ideas??

Thanks so much


A B C
e33e candy 100
f33f corn 7876
h44h hammer 2365
j66j box 9854
e33e candy 99998
j66j box 13221

[wakeandbake]

Sorry, this should make the bottom clearer.

A                 B                  C 
e33e            candy           100 
f33f             corn             7876 
h44h            hammer         2365 
j66j             box               9854 
e33e           candy            99998 
j66j             box               13221  

[Fotis1991]

Hi

Maybe, try without absolute reference...


=AND($A1="e33e", $F$5="e33e"), TRUE, FALSE)

[wakeandbake]

I tried that before and it did not work, but i just deleted all the rules I because I had like 10 practice ones running, and tried again, and it looks like it works!

Of course, i am cautiously optimistic, but I'll get back if something goes wrong.

Thanks a bunch!

Go greece!

[wakeandbake]

I tried that before and it did not work, but i just deleted all the rules I because I had like 10 practice ones running, and tried again, and it looks like it works!

Of course, i am cautiously optimistic, but I'll get back if something goes wrong.

Thanks a bunch!

Go greece!

[wakeandbake]

Does anyone know how to get conditional formatting to pick up multiple characters. For instance, this is the function I am using to highlight the key I need when its in a specific cell.

=IF(AND($A$1="col3", $F$5="col3"), TRUE, FALSE)


However, I want to search all of column A for any cell that contains just the three letters "col". So if i select "col" from the combo box, all the keys that begin with col (say, col3,col4,col5....) all get highlighted as well.


Thanks again guys

[Fotis1991]

Hi

Do you mean something like this?

[wakeandbake]

Sort of. Check this out:


[ATTACH] conditional format example [/ATTACH]


If you select "col5" or "col7" in the combo box, then the keys "col5" or "col7" in the rows to the left highlight red. However, I also want to select the term "col" in the combo box and have all the keys with the phrase "col", irrespective of the number after it, also to highlight red. So, if I select col, then col5, col7, and any other one that begins with col, will also highlight red. that is where I am stuck.

Thanks again for the help!

[Fotis1991]

Using same logic..