View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
James[_14_] James[_14_] is offline
external usenet poster
 
Posts: 25
Default 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)