force text in a cell based on value of cell in a range
Something like this should do the trick:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rngValidCells As Range
Dim Rng As Range
On Error Resume Next
Set rngValidCells = Intersect(Target, "FY04_reduction_totals")
If Not rngValidCells Is Nothing Then
For Each Rng In rngValidCells
If Rng.Value < 0 And Range.Offset(0, -7).Value = "" Then
MsgBox "Please enter a description"
End If
Next Rng
End If
End Sub
If the range "FY04_reduction_totals" is a column, it would be more robust to give a name to the column containing the descriptions, such as "FY04_descriptions" and replace
Range.Offset(0, -7).Value
with
Intersect(Rng.EntireRow, Range("FY04_descriptions")).Value
because the code doesn't rely on there being 6 columns between the two you're interested in (you can insert/delete columns without changing the code)
|