SOLVED

# Sum shown in colour to be higher or lower than a target number

Copper Contributor

# Sum shown in colour to be higher or lower than a target number

Hi,

If I have cell 3 showing the result of a deduction of a number in cell 2 from a number in cell 1, is there a way for cell 3 to be instructed to give this result but turn either the text of the number or the cell itself to the colour red if the value is higher than a value of say"10" referenced as a target number to reach in another cell 4.

Conversely, if the value is lower than "10" in cell 4, cell 3 would be instructed to show this lower number result but either the text or the cell will turn to the colour green.

4 Replies
best response confirmed by mathetes (Silver Contributor)
Solution

# Re: Sum shown in colour to be higher or lower than a target number

@gnasher That can be done with Conditional Formatting as shown in the picture.

I selected cells C3:C7. Then on the Home ribbon, choose Conditional Formatting.

Highlight Cells, greater than.....

and then enter 10 and set the format to your liking.

# Re: Sum shown in colour to be higher or lower than a target number

Thanks Riny, I used your advice and by using the "greater than equal" function was able to highlight when the target was not achieved.

Pushing a little further with your advice I noticed that it does not seem to be possible to apply two simultaneous rules to the same cell though ("greater than and equal" and "less than and equal"). Do you know if it is possible to do this?

# Re: Sum shown in colour to be higher or lower than a target number

@gnasher Yes you can. If a value has to be both >= AND <=, that would be a "between" rule. Otherwise you need to use a formula to set the rule with, for example the AND function. There are many possibilities with Conditional Formatting. Google for it and you'll find numerous examples.

# Re: Sum shown in colour to be higher or lower than a target number

Thanks Riny, thats great advice and useful to know. So far I had been stuck on single instructions in a cell.
1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution

# Re: Sum shown in colour to be higher or lower than a target number

@gnasher That can be done with Conditional Formatting as shown in the picture.

I selected cells C3:C7. Then on the Home ribbon, choose Conditional Formatting.

Highlight Cells, greater than.....

and then enter 10 and set the format to your liking.