Change color in cell with multiply reference cells

[karltomm]

Hi!

I have got stuct in my try to get a cell to change color when I have three cells that is a part of the condition for changing.
I have tryed some conditionall formating and I have try to write a function but do only get it to work half on half. The problem is as follow.

I have columns A,B and C. They can have values of 1 to 10. in column D i have the result. D=A*B*C.
Depending on the result in D and what number A have I want to change the color on the D cell to red or green and have the result showing.
Case 1. If A is 1-5 and D is below 100 then D should be green, above 100 it is red.
Case 2. If A is 6-8 and D is below 50 then D should be green, above 80 it is red.
Case 3. If A is 9-10 and D is below 10 then D should be green, above 10 it is red.

Do anyone have a ide how I should build this function?

/Tommy

 

[Gerald Higgins]

Conditional Formating is the way to go, use this formula

=OR(AND(A1<=5,D1<100),AND(A1<=8,D1<50),D1<10)

and apply a green fill colour.

Make the default fill colour red, to apply when the above evaluates to FALSE.

 

[karltomm]

Hi!

That was a nice function, much better then the monster of IF I did. But I now get the problem that this only work for line 1 and if I copy the conditional format to line two and three and so on they will still refere to line one. The conditional format does not seem to change to the line where it is copied to like if I had wrote this formula in the cell direct and then copy it to the next lines. Is there a way around this problem?

/Tommy

 

[Gerald Higgins]

Do you have exactly what I posted in post #2, or does your formula include $ symbols ? If it does have $$$, experiment with removing them, especially the ones immediately before the row numbers.

 

[karltomm]

Hi!

Yes I did a direct copy but I had to change "," to ";" to get it to work so there are no $.

I did some other test and the conditionall format does not change the formula when copy. So then I need to sit and write a new conditionall format for every line and that is a pain if I need to change in the future.

/Tommy

 

[Gerald Higgins]

OK, what version of Excel are you using ?

I've tested this in Excel 2007, and if you use this formula and set it up for one cell, then use Copy, Paste Special, Formats, it copies the row references but adjusts them downwards, so in row 1 it refers to row 1, in row 2 it refers to row 2, and so on.
I'm pretty sure it does the same in Excel 2003.

 

[karltomm]

Hi!

I have 2010, now it works I did not use the paste special when I copy the cells. I pull in the corner of the cell and down, then it does not work.
Small things makes a lot of difference some times.

Many thanks for your help!

/Tommy