Excel VBA returing a value of a cell

Question

Hopefully someone will be able to help me with this. I am completely new to the VBA and coding. Here is my code below:

Private Sub Workbook_Open()
For Each Cell In Range("I2:I500")


If Cell.Value < Date - 9 And Cell.Value <> "" Then
    If Cell.Font.ColorIndex <> 22 And Cell.Interior.Color <> RGB(151, 210, 86) Then
        MsgBox "Status 1 " & ***Cell.Address(False, False)***
        Cell.Font.ColorIndex = 22
        Cell.Font.Bold = True
End If
ElseIf Cell.Value < Date - 5 And Cell.Value <> "" Then
    If Cell.Font.ColorIndex <> 55 And Cell.Interior.Color <> RGB(151, 210, 86) Then
        MsgBox "Status 2 " & ***Cell.Address(False, False)***
        Cell.Font.ColorIndex = 55
        Cell.Font.Bold = True
End If
ElseIf Cell.Value < Date - 4 And Cell.Value <> "" Then
    If Cell.Font.ColorIndex <> 41 And Cell.Interior.Color <> RGB(151, 210, 86) Then
        MsgBox "Status 3 " & ***Cell.Address(False, False)***
        Cell.Font.ColorIndex = 41
        Cell.Font.Bold = True
End If
ElseIf Cell.Value < Date - 2 And Cell.Value <> "" Then
    If Cell.Font.ColorIndex <> 33 And Cell.Interior.Color <> RGB(151, 210, 86) Then
        MsgBox "Status 4 " & ***Cell.Address(False, False)***
        Cell.Font.ColorIndex = 33
        Cell.Font.Bold = True
End If
Else
Cell.Font.ColorIndex = 1
Cell.Font.Bold = False

End If
Next

For Each Cell In Range("O2:O500")

If Cell.Value < Date - 30 And Cell.Value <> "" Then
    If Cell.Font.ColorIndex <> 22 And Cell.Interior.Color <> RGB(151, 210, 86) Then
        MsgBox "30 Days " & ***Cell.Address(False, False)***
        Cell.Font.ColorIndex = 44
        Cell.Font.Bold = True
End If
ElseIf Cell.Value < Date - 60 And Cell.Value <> "" Then
    If Cell.Font.ColorIndex <> 55 And Cell.Interior.Color <> RGB(151, 210, 86) Then
        MsgBox "60 Days " & ***Cell.Address(False, False)***
        Cell.Font.ColorIndex = 46
        Cell.Font.Bold = True
End If
ElseIf Cell.Value < Date - 90 And Cell.Value <> "" Then
    If Cell.Font.ColorIndex <> 41 And Cell.Interior.Color <> RGB(151, 210, 86) Then
        MsgBox "90 Days " & ***Cell.Address(False, False)***
        Cell.Font.ColorIndex = 3
        Cell.Font.Bold = True
End If
Else
Cell.Font.ColorIndex = 1
Cell.Font.Bold = False

End If
Next

For Each Cell In Range("L2:L500")

If Cell.Value = "NO" Then
        MsgBox "Actione has not been taken " & ***Cell.Address(False, False)***

        Cell.Font.ColorIndex = 3
        Cell.Font.Bold = True
        Cell.Font.Underline = True
End If
If Cell.Value = "YES" Then
        Cell.Font.ColorIndex = 1
        Cell.Font.Bold = False
        Cell.Font.Underline = False
End If
Next

For Each Cell In Range("N2:N500")

If Cell.Value = "NO" Then
        MsgBox "Actione has not been taken " & ***Cell.Address(False, False)***
        Cell.Font.ColorIndex = 3
        Cell.Font.Bold = True
        Cell.Font.Underline = True
End If
If Cell.Value = "YES" Then
        Cell.Font.ColorIndex = 1
        Cell.Font.Bold = False
        Cell.Font.Underline = False
End If
Next
End Sub

It does not do much, just pops up Message Box when criteria is met. What I was hoping to do is along with the message within the message box to return value of a cell from the same row but different column. I have highlighted with *** code which currently returns an address of an active cell, but as I explained earlier I would like this to be value of a different cell from the same active row but from column "F". I have tried to use range function but it did not work. Can someone please assist me with this?

Thank you and any help will be appreciated.

Answer 1

You are going to want to use the Range.Offset() method http://msdn.microsoft.com/en-us/library/office/ff840060%28v=office.15%29.aspx

Because you are checking each Cell, then the variable Cell is your Range. So you would write:

Cell.Offset(0, Number of columns offset from current location).Value

Alteratively you could use a different method if you always want column F:

Range("F" & Cell.Row).Value