CODEWITHSUNDEEP
vbaexcelrangemessagebox
Tombies
Tombies

Reputation: 15

Display message box of cell value if a corresponding cell value is equal to criteria

I need help trying to program excel VBA. There are two ranges, H3:H100 & A3:A100

I'm trying to do the following:

On Workbook open, if a cell in range H3:H100 = "PERMIT EXPIRES WITHIN 7 DAYS", then display a message box showing the corresponding "A" column cell value saying: "Permit (Column A cell value) Expires in 7 days"

This is the code that I have:

Private Sub Workbook_Open()
For Each cell In Range("I3:I100")
If cell.Value = "Y" And cell.Value <> "" Then
cell.Interior.ColorIndex = 15
cell.Font.ColorIndex = 10
cell.Font.Bold = True
ElseIf cell.Value = "N" And cell.Value <> "" Then
cell.Interior.ColorIndex = 22
cell.Font.ColorIndex = 2
cell.Font.Bold = True
End If

Next
For Each cell In Range("H3:H100")
If cell.Value = "PERMIT EXPIRES WITHIN 7 DAYS" And cell.Value <> "" Then
MsgBox "Permit (" & cell.Row & ":" & cell.Column & ") Expires in Seven Days", vbExclamation, "Alert"
End If
Next
End Sub

It does work, however it displays the intersecting row and column number, not the actual cell value in the "A" range.

Any help is appreciated.

Upvotes: 0

Views: 4173

Answers (1)

lc.
lc.

Reputation: 116528

Use the Cells property to get the value of the cell in column A given the row index:

Cells(cell.Row, 1).Value

Indices are 1-based, so the 1 here means column A.

Upvotes: 0

Related Questions