 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / February 2006code wont compare negative numbers correctly
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 Just bumping this to see if I can get any help 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  SignatureRegards, Tom Ogilvy > Just bumping this to see if I can get any help. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.