vba question

[A1058]

hello guys im wondering if someone can help
id like to with vba have it look at cells A2:A31 and A37:A51, and if these cells <> "" then S2:S31 and S37:S51 should also have a value. so whatever cells in column A in the range listed have values the corresponding cells in column S should also have a value and IF they do not have a value in column S i need sheet printing to be disabled and to popup a msgbox saying Column S values not entered . once column S values are all ok then reenable sheet printing

thans a lot guys

 

[HalfAce]

Hello A1058,
Try something like this in the ThisWorkbook module.
(Will only test if it's sheet2 that's trying to be printed. Change that to suit.)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet2" Then
  Dim Rng As Range, c As Range
  Set Rng = Range("A2:A31", "A37:A51")
  For Each c In Rng
    If Not IsEmpty(c) And IsEmpty(c(, 19)) Then
      Cancel = True
      MsgBox ("There is a value in " & c.Address(0, 0) & " with no value in " & c(, 19).Address(0, 0)), , "Missing Data in Column S"
      Exit Sub
  End If
  Next c
End If
End Sub