PDA

View Full Version : [SOLVED] Range Selection Then apply formula with third column over



guitarsweety
07-29-2015, 06:04 AM
I'm using the code below to change the interior cell colors of a cell if Column H is less than Column K. It works great until I have to add another column for an additional inventory item.

Can anyone help me revise this code to maybe select the first column and then do the less than column 3 columns over.14028





Sub macro7()'Inventory
If Environ("UserName") = "dwood" Or Environ("UserName") = "rwagner" Then
Dim rng As Range
For Each rng In Range("H7:H34")
If Cells(rng.Row, "H") < Cells(rng.Row, "K") Then
rng.Interior.Color = RGB(255, 199, 206)
MsgBox "Check Inventory Balance in cell " & Cells(rng.Row, "H").Address(0, 0) & ".", vbExclamation + vbOKOnly + vbApplicationModal, "Insteel Wire Products"
End If
Next rng
If rng Is Nothing Then
MsgBox "All other inventory balances are at a good level", vbInformation + vbOKOnly + vbApplicationModal, "Insteel Wire Products"
End If
Else
MsgBox "Only The Business Unit Assistant can access this function!", vbCritical, "Insteel Wire Products"
End If
Exit Sub
End Sub

Aflatoon
07-29-2015, 07:45 AM
Perhaps:

For Each rng In Range("H7:H34")
With rng
If .Value < .Offset(, 3).Value Then
.Interior.Color = RGB(255, 199, 206)
MsgBox "Check Inventory Balance in cell " & .Address(0, 0) & ".", vbExclamation + vbOKOnly + vbApplicationModal, "Insteel Wire Products"
End If
End With
Next rng

guitarsweety
07-29-2015, 07:59 AM
Thank you so much, is there a way to incorporate a selected range instead of H7:H34?

Aflatoon
07-29-2015, 08:11 AM
Sure - just change this line

For Each rng In Range("H7:H34")
to this

For Each rng In Selection.Cells

guitarsweety
07-29-2015, 08:27 AM
Thank you so much, that's exactly what I needed.