Make Column Mandatory if another cell in same row has data in it.

**mweick** · 01-28-2014, 10:28 AM

I am trying to write some VBA to make a column mandatory only if there is data in a different column.

Here's my problem:

In worksheet "For Sales", If column B contains data, then make column AW a mandatory field for the associated row.

I have tried multiple different combinations of logic/code and I can't get this to work. I would like to assign this macro to a button if possible.

Any help/advise is greatly appreciated.

Thanks,

Matt

**Mumps1** · 01-28-2014, 10:56 AM

Hi Matt. Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim InputVal As String
    Dim rng As Range
    For Each rng In Range("B2:B" & LastRow)
        If rng <> "" And Range("AW" & rng.Row) = "" Then
            Do
                InputVal = InputBox("Please type an entry for cell : " & "AW" & rng.Row & ".")
                If InputVal <> "" Then
                    Range("AW" & rng.Row) = InputVal
                    Exit Do
                ElseIf InputVal = "" Then
                    MsgBox "You must type an entry for cell: " & "AW" & rng.Row & ".", vbOKOnly, ""
                Else: Exit Do
                End If
            Loop
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

**stnkynts** · 01-28-2014, 11:16 AM

Sub RunMe()
Dim ws As Worksheet:    Set ws = Sheets("For Sales")
Dim iBox As String
Dim c As Range

For Each c In ws.Range("B1:B" & ws.Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
    If ws.Range("AW" & c.Row).Value = "" Then
reEnter:
        iBox = InputBox("Enter a value for cell AW" & c.Row)
        If iBox = "" Then
            MsgBox ("You must enter a value for the cell")
            GoTo reEnter
        Else
            ws.Range("AW" & c.Row).Value = iBox
        End If
    End If
Next c
        
End Sub