Insert First Row in Table with logic for Data Validation and Conditional Formatting

Question

I have a Conditional Formula for a table that requires a user to fill out a required cell, the issue is once this cell is highlighted I am unable to save it, due to some VBA preventing saving if that cell is not filled out.

I have some VBA that dynamically adds rows, and was checking to see if there was a way to incorporate the Conditional Formula and any Data Validation for the row, forcing the user to click that 'Add Row' button with all the formulas and Data Validations.

I've tried to include a Sample Row with each cell in a column containing the logic, but the user never changes that first row, which isn't real data.

''The code below adds a table row to the table.

Sub AddTableRow()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Range("Table2[Line Number]").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
        Range("E14").Select
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

''The code below is looking for any cell with the orange/red color and prevents the Workbook_BeforeSave event in Excel.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


Dim rng As Range

For Each rng In Worksheets(1).UsedRange
    If rng.DisplayFormat.Interior.Color = 49407 Or rng.DisplayFormat.Interior.Color = vbRed Then
        MsgBox ("Please correct any fields in red/orange")
        Cancel = True
        Application.ScreenUpdating = True
        Exit Sub
    End If
Next rng

End Sub

So in summary, I'd like to add a Row to a table with all the Conditional Formatting and Data Validations without having an 'Existing' row using VBA.

I think you are looking for something like this to add conditional formating: Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=ISERROR(MATCH($A$1,List,0))" Where list is a named range for your compare values — mooseman

Tim Williams Tim Williams · Accepted Answer · 2019-06-25T20:48:58

I'm not sure I'm completely clear on the problem, but one approach might be to modify your BeforeSave code to ignore any table rows which are completely empty:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim rng As Range, rw As range, c as Range, clr as Long

    Set rng = me.listobjects("Table2").DatabodyRange
    For each rw in rng.Rows
        'any content in this row ?
        If application.counta(rw) > 0 Then
            'check for validation errors
            For Each c in rw.cells
                clr = c.DisplayFormat.Interior.Color
                If clr = 49407 Or clr = vbRed Then

                    MsgBox ("Please correct any fields in red/orange")
                    Cancel = True
                    Exit Sub

                End If
            Next c
        End If
    Next rw

End Sub

...or just have it ignore anything in the first "sample" row.

Insert First Row in Table with logic for Data Validation and Conditional Formatting

1 Answers