Changing cells colors in relation to cell input - NOT conditional formatting ques.

[jj72uk]

Hi guys, hope someone here can help me out!

I'm a average excel user, i know my way around it and kow common and basic formulee, VB isnt a strong point, but i am willing to attemp anything for this, let me explain

Lets say in colum A I have a list of items, in colum B i have a check box, currently the check box is red, if i tpye a x it turns green due to conditional formating. That works fine...onto the problem...

Lets say in colum A i have my items but they are repeated...eg A1, A145, A166, A122 are all the same item, what i want is regardless of where i place the x each check box under coloum B either gets a X and turns green, or just turns green, i dont mind...

I dont think conditional formatting can cut this, though i may be wrong. Ive tried to use the IF function under condtional formatting but ran into problems...

Any help or direction would be great!

[deadlyduck]

The following might help although I don't use "check boxes" (the little boxes you can tick) to place the "x". I am assuming that you can use blank cells and enter an "x" into one of them which in turn will fill in all of the other relevant cells.

You need to insert a 'Helper column'. This MUST be at the left of the values that you currently have in column A (so all of your data currently in column A will now be in column B and your "automatic x marks" will be displayed in column C). The idea of this helper column is that you'll type an "x" into a cell 9ssay A7) and if there are repetitions of the value contained in cell B7 in any other B cells that an X will be automatically entered into the corresponding column C cells.

If you type the following formula into cell C1 (assuming that your data is contained in the range A1 to B5) see how it works:

Please Login or Register  to view this content.

However, I couldn't make it work for multiple repetitions of different numbers eg where multiple cells contain the value 345 and some other multiple cells contain 678 it won't display ALL the repetitions of ALl values- it'll only show an x for ALL 345 values OR all 678 values but not both simultaneously...sorry!

[jj72uk]

hmm intresting...

So this kinda works, but then doesn't. I havent tested it out yet as the spreadsheet is at work.

But from whta i can gather it will only work once, for the first X you'd put in Column A.

So in column B if i have the word 'milk' repeated 6 times, and the word 'eggs' repeated 6 times, and the word 'butter' 6 times, i would only update column C ( the 'check' box column ) of the first instant...

Is there anyone else with ideas?

[darkyam]

For the conditional format formula, =SUMPRODUCT((A:A=A1)*(B:B))>0. With this, for any given row, if the value in A of that row appears anywhere in A and has checkbox next to it that is checked (I'm assuming the value of the checkbox is also in B, either TRUE or FALSE), then this will format any cell with that value in A as you want it. There is no non-macro way to change the value itself, as a formula cannot refer to the cell it is in.

[jj72uk]

that sounds promising, i'll have to try it out...my check boxes arent the littel box you click and a x appears, what i mean is a cell with the letter x written in it.

would this make any differnece?

[darkyam]

Change formula to =SUMPRODUCT((A:A=A1)*(B:B="x"))>0.

[jj72uk]

OK..thats not exactly it...

let me try to explain clearer...

In column A lets say i have a series of number in each cell, within this list of numbers, some repeat themselves...eg in A2, A34, A78 they ALL have the same number of 100.

What I want is to type a 'x' next to one of them, for example A34, so i'd be entering a 'x' in B34 and for the spread sheet the automatcailly update all the other cells with the value '100' with a 'x' nect to them, so i this case, if i enter a x into b34, the sheet with put an x in a2, a78 for me ( i can use conditional formatting then to chnage cell color in accordance with it being blank or having a x)

[darkyam]

Your first post had said it would be ok if it just made the cells green. There is no non-macro way to do what you are asking as you would have circular references. You could put the formula I gave you in a helper column and it would return TRUE or FALSE or you could modify it into an IF formula to return x or blank, but putting the values in column B is not possible without VB.

[jj72uk]

Is there a automated way to run macro's?

In my last post i said i wanted to type a 'x' into eg cell b24 as A34 had the value of 100 in it, and what i wanted was for the sheet to automactically update the other b cells with 100 in the corresponding A cells with a 'x' though this would be great, i can live with not getting a 'x' and just the box turning green instead.

so eg id have A2, A34, A78 all having the value of 100 and if i put a 'x' in b78, b2 and b34 would turn green, and vice versa depending wha cell i'd enter the 'x' into first, so it woudl still work if i had 'x'd the A2 box first or the A34 first.

[darkyam]

Most macros run in the background and update whenever a cell within their range is changed. I don't know enough of VBA to write this, but there are a bunch of people here who do.