Macro to compare cell values

[Zimmerman]

Does anybody know how to create a macro that will loop through the active sheet and compare the cell values in Cells O8:O37 against cells P8:P37? If the cell value in cells o837 are lower than the P8:P37 then a warning will pop up stating which cells are lower.
Thanks
Windows XP
Excel 2003

 

[JackDanIce]

Try:

Sub CompareOandP ()
Dim i as Long, j as Long
For i = 8 to 27
    If Range("O" & i) <> Range("P" & i) Then
       If Range("O" & i) > Range("P" & i) Then
          MsgBox "Cell P" & i & " is smaller than cell O" & i
       Else
          MsgBox "Cell O" & i & " is smaller than cell P" & i
       End If
    End If
Next i
End Sub

 

[Zimmerman]

That works great thanks. the only thing i am wondering now is when the warning comes up, is there a way to have it display all the cells on one warning rather than displaying a warning for each cell? This way the user will only need to see all the cells at once.

 

[JackDanIce]

Like this?

Sub CompareOandP2()
Dim i As Long
Dim MsgO As String, MsgP As String
For i = 8 To 27
    If Range("O" & i) <> Range("P" & i) Then
       If Range("P" & i) < Range("O" & i) Then
          MsgP = MsgP & Range("P" & i).Address & vbCrLf
       Else
          MsgO = MsgO & Range("O" & i).Address & vbCrLf
       End If
    End If
Next i
MsgBox "These cells in column P are smaller than" & vbCrLf & vblcrf & "The equivalent ones in column O:" _
        & vbCrLf & vbCrLf & MsgP
MsgBox "These cells in column O are smaller than" & vbCrLf & vblcrf & "The equivalent ones in column P:" _
        & vbCrLf & vbCrLf & MsgO
End Sub

 

[mikerickson]

If highliting cells is as acceptable, rather than a message box with addresses, Conditional Formatting could be used.

 

[Zimmerman]

Highlighting would be an option but I'd like to have this macro to compare differnet cells when a different button is pressed. So a button to compare cells o against p then a button to compare cells s against t.

But the above macro seems to work perfect now so thanks for all the help. It works just as i wanted it too.

Thanks

 

[JackDanIce]

Depending on how good your macro reading skills are, it should be fairly straight forward on how to adapt the code for columns S & T. Good luck, glad it worked

 

[Zimmerman]

Yep i think i should be able to figure that part out.
But let me throw a tough one at you. What I'm trying to accomplish is when the user hits this button it's actually telling the user that they don't have enough parts. Hence the reason why i wanted to get all the cells on one warning. is there a way to have this look at another column with numbers and tell the user that they need to get at least a certain amount coompleted to get the number that is below the other up to the correct amount?

I'm more than happy to share this worksheet with you if you'd like. I'm probably wanting to much on this but in a nut shell I'm trying to get this to look at where the parts are in inventory and tell the user which parts need to move to the next step.

 

[JackDanIce]

Better to post a sample of your sheet to the thread or try to be more precise (e.g. use cell references and examples), then others can help if I can't

 

[Zimmerman]

I'm not sure that i know how to reply and insert my sheet.