code wont compare negative numbers correctly


E

evil baby

I had to format a row of numbers depending on the value above it.


I have to compare A2 to A1 and change A2 accordingly.

Problem is when A1 is a negative number the conditional formattin
doesn't work.

Here is my code: I'm pretty new at this so it is probably really ugly


Dim x
Range("G22").Select
Do While ActiveCell <> 0
x = ActiveCell.Offset(-1, 0).Value
'MsgBox x
'MsgBox ActiveCell.Value
If ActiveCell.Value >= (x + (x * 0.05)) Then
ActiveCell.Interior.ColorIndex = 3
ActiveCell.Font.ColorIndex = 2
ElseIf ActiveCell.Value <= (x - (x * 0.05)) Then
ActiveCell.Interior.ColorIndex = 3
ActiveCell.Font.ColorIndex = 2
ElseIf ActiveCell.Value >= (x + (x * 0.02)) Then
ActiveCell.Interior.ColorIndex = 36
ActiveCell.Font.ColorIndex = 1
ElseIf ActiveCell.Value <= (x - (x * 0.02)) Then
ActiveCell.Interior.ColorIndex = 36
ActiveCell.Font.ColorIndex = 1
ElseIf ActiveCell.Value < (x + (x * 0.02)) Then
ActiveCell.Interior.ColorIndex = 35
ActiveCell.Font.ColorIndex = 1
ElseIf ActiveCell.Value > (x - (x * 0.02)) Then
ActiveCell.Interior.ColorIndex = 35
ActiveCell.Font.ColorIndex = 1
End If
ActiveCell.Offset(0, 1).Select
Loop
End Su
 
Ad

Advertisements

T

Tom Ogilvy

Sub aaTest()
Dim x
Range("G22").Select
Do While ActiveCell <> 0
x = ActiveCell.Offset(-1, 0).Value
y = ActiveCell.Value
'MsgBox x
'MsgBox ActiveCell.Value
x = Abs(x)
y = Abs(y)
If y >= (x + (x * 0.05)) Then
ActiveCell.Interior.ColorIndex = 3
ActiveCell.Font.ColorIndex = 2
ElseIf y <= (x - (x * 0.05)) Then
ActiveCell.Interior.ColorIndex = 3
ActiveCell.Font.ColorIndex = 2
ElseIf y >= (x + (x * 0.02)) Then
ActiveCell.Interior.ColorIndex = 36
ActiveCell.Font.ColorIndex = 1
ElseIf y <= (x - (x * 0.02)) Then
ActiveCell.Interior.ColorIndex = 36
ActiveCell.Font.ColorIndex = 1
ElseIf y < (x + (x * 0.02)) Then
ActiveCell.Interior.ColorIndex = 35
ActiveCell.Font.ColorIndex = 1
ElseIf y > (x - (x * 0.02)) Then
ActiveCell.Interior.ColorIndex = 35
ActiveCell.Font.ColorIndex = 1
End If
ActiveCell.Offset(0, 1).Select
Loop
End Sub
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top