Condition Formatting with different sheet

[Kim]

Hi,

Can anyone help me to find a way round the problem I've. I'm trying to set a
condition formattion to a cell where if there is any changes it will change
colour.

Example: The original value is a cell is 50. And if I change to a higher
value, the cell should turn green and if I change to lower value it will
change to red. The original cell should be no fill.

Thanks,
Kim

 

[Dave Curtis]

Hi,
Just apply conditional formatting with condition 1 as Cell value is greater
than 50, format it to green, and condition 2 as Cell value is less than 50,
format it to green.

What is the different sheet in your thread title? conditional formatting
based on a different sheet requires you to define a named range.

Dave

 

[Kim]

Hi Dave,

I need to set the condition formatting to the whole column and the original
value is different.

Is there a simplier way or do I have to set one cell by one cell?

Regards,
Kim

 

[Dave Curtis]

Hi,
You mean the value of 50 is different for each cell?
Are they random, or defined by something else?


Dave

 

[Bob I]

You need to put the 50 into the conditional format. >50=green <50=red
therefore 50 = not red or green by default.

 

[Kim]

Here is an example:-

Original value in the cell
A
1 50
2 70
3 80
4 55
5 60

If I change cell A1 to 55 it should turn green.
If I change cell A2 to 60 is should turn red.
If I change cell A3 to 75 it should turn red.

Regards,
Kim

 

[Kim]

Here is an example:-

Original value in the cell
A
1 50
2 70
3 80
4 55
5 60

If I change cell A1 to 55 it should turn green.
If I change cell A2 to 60 is should turn red.
If I change cell A3 to 75 it should turn red.

Regards,
Kim

 

[Dave Curtis]

Hi Kim, Consider using a helper column.
Copy your values from A into B
Highlight all the numbers in B and then apply the conditional formatting.
Condition 1 as formula is =B1<A1, format to green.
Condition 2 as formula is =B1>A1, format to red.
All cells in the range should then have the correct references.
You can then hide column A.

Dave

 

[Bob I]

See Dave's reply using Helper cell to hold the comparison value.