Solved: Green if zero, Red if not

[blackie42]

Hi

Trying to write a function that subtracts once cell from another and turns the background red if not zero. F6 & F20 are the results of the additions of 2 other columns (so F22 is F6(total of F1 to 5) - F20(total of F10 to F19)). My problem is that even if the result is zero it still turns red. Can anyone help or suggest a better method?

Function check()
Dim r As Range
Set r = Range("f22")
r.Name = "Total"
Range("Total").Value = "=sum(f6-f20)"
Range("Total").Interior.Color = RGB(193, 255, 193)
If Range("Total").Value <> 0 Then
Range("Total").Interior.Color = RGB(255, 0, 0)
End If
End Function

[JimmyTheHand]

Your code looks OK to me.
Do you see all decimals in F22? Maybe the real value is not 0, it only looks like 0.

Anyway, I think you should use conditional format instead of hardcoding cell colour.

Jimmy

[Bob Phillips]

Could it be that you want it dynamic, and it doesn't change to green when the total goes 0?

JTH is correct, use conditional formatting.

[blackie42]

Using conditional formatting - only way I can get it to work is using the 3 conditions i.e. > 0 = red, < 0 = red, O = black - however when I test it the 0.00 condition doesn't turn black but remains as red - and i can't change it with the toolbar icon - it appears to be stuck on red 0.00.

If i remove the formatting it does turn back black

thanks

[Bob Phillips]

<> 0 red

or

ABS(cell)=0

[blackie42]

Hi XLD,

Sorry to be a pest but not used cond formatting much. Where would I put the references you suggest? The 2nd field dropdown doesn't have a <> qualifier.

thanks

[blackie42]

Sorry being stupid again <> means not equal to of course - but the cell turns red anyway - the actual value displayed is 0.00 on 'number' format - if I change cell format to general i get -8.52651E-13Is this the cause of the problem?

thanks

[tinyjack]

Yep, that will be the problem. The comparision = means exactly equal and you would need to introduce an approximately equal, so something like:

If Round(Range("Total").Value,4) <> 0 Then

or what I would usually do change your SUM so that it is wrapped with a ROUND.

HTH

TJ

[tinyjack]

One other small point, since you are not returning anything I would do this with a Sub rather than a Function.

TJ

[blackie42]

Thanks for all the input guys

regards

Jon

[blackie42]

Forgot to say the round to 4 places seems to have sorted out the code.

thanks again