[RESOLVED] Adding a border to a cell within a table range when values are missing

[scoobster]

Hello Forum

I have an Excel table range and I want to flag where data is missing after a user clicks a button to kick off some processing. All I need is a way to identify the offending cell(s) and then how to set the border to highlight the cell to the user.

Table range is called 'Library'.

Is is possible?

Thanks.

[vbfbryce]

something quick to try out:

Sub myTable()
    Dim dTable As DataTable
    Dim ws As Worksheet
    Dim lObj As ListObject
    Dim r As Integer
    Dim c As Integer
    
    Set ws = ActiveSheet
    Set lObj = ws.ListObjects("Table1") 'my table is Table1, change to "Library"
    For r = 1 To lObj.DataBodyRange.Rows.Count
        For c = 1 To lObj.DataBodyRange.Columns.Count
            If lObj.Range(r, c).Value = "" Then
                MsgBox "Row " & r & " column " & c & " is blank"
                With lObj.Range(r, c).Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With lObj.Range(r, c).Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With lObj.Range(r, c).Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With lObj.Range(r, c).Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
            End If
        Next c
    Next r
End Sub

[scoobster]

That's great. Many thanks. If I wanted to check only columns 3,4,6,9,12 rather than all, can the loop be modified?

Thanks again.

[vbfbryce]

Sure. Updated with "select case" to check for column:

Sub myTable()
    Dim dTable As DataTable
    Dim ws As Worksheet
    Dim lObj As ListObject
    Dim r As Integer
    Dim c As Integer
    
    Set ws = ActiveSheet
    Set lObj = ws.ListObjects("Table1") 'my table is Table1, change to "Library"
    For r = 1 To lObj.DataBodyRange.Rows.Count
        For c = 1 To lObj.DataBodyRange.Columns.Count
            Select Case c
                Case 3, 4, 6, 9.12
                    If lObj.Range(r, c).Value = "" Then
                        MsgBox "Row " & r & " column " & c & " is blank"
                        With lObj.Range(r, c).Borders(xlEdgeLeft)
                            .LineStyle = xlContinuous
                            .ColorIndex = xlAutomatic
                            .TintAndShade = 0
                            .Weight = xlThin
                        End With
                        With lObj.Range(r, c).Borders(xlEdgeRight)
                            .LineStyle = xlContinuous
                            .ColorIndex = xlAutomatic
                            .TintAndShade = 0
                            .Weight = xlThin
                        End With
                        With lObj.Range(r, c).Borders(xlEdgeTop)
                            .LineStyle = xlContinuous
                            .ColorIndex = xlAutomatic
                            .TintAndShade = 0
                            .Weight = xlThin
                        End With
                        With lObj.Range(r, c).Borders(xlEdgeBottom)
                            .LineStyle = xlContinuous
                            .ColorIndex = xlAutomatic
                            .TintAndShade = 0
                            .Weight = xlThin
                        End With
                    End If
                Case Else
                    'not one of the colums to check, do nothing
            End Select
        Next c
    Next r
End Sub

[scoobster]

Almost perfect. It works but I wonder if I could lose the hardcoded columns in the select statement? Can I read those from a cell on the workbook?

Thanks for you help.

[vbfbryce]

Could do that, yes.

How many columns wide will your table be at most?

[scoobster]

19 columns wide as of now. Shouldn't grow any wider.

[vbfbryce]

I put the column numbers that I want to check in cell Z1 in this format:

1,3,4,7,11

Separated by commas, no spaces, etc.

Here is updated code:

Sub myTable()
    Dim dTable As DataTable
    Dim ws As Worksheet
    Dim lObj As ListObject
    Dim r As Integer
    Dim c As Integer
    Dim x As Integer
    '*********************
    Dim checkCols() As String
    '*********************
    
    Set ws = ActiveSheet
    '*********************
    checkCols = Split(ws.Range("z1").Value, ",")    'populate array with column numbers to check
    'get these values from cell z1, in this format:  1,3,4,9
    '*********************
    
    Set lObj = ws.ListObjects("Table1") 'my table is Table1, change to "Library"
    For r = 2 To lObj.DataBodyRange.Rows.Count + 1
        
        For c = 1 To lObj.DataBodyRange.Columns.Count
            For x = 0 To UBound(checkCols)
                If c = checkCols(x) Then
                    If lObj.Range(r, c).Value = "" Then
                        MsgBox "Row " & r & " column " & c & " is blank"
                        With lObj.Range(r, c).Borders(xlEdgeLeft)
                            .LineStyle = xlContinuous
                            .ColorIndex = xlAutomatic
                            .TintAndShade = 0
                            .Weight = xlThin
                        End With
                        With lObj.Range(r, c).Borders(xlEdgeRight)
                            .LineStyle = xlContinuous
                            .ColorIndex = xlAutomatic
                            .TintAndShade = 0
                            .Weight = xlThin
                        End With
                        With lObj.Range(r, c).Borders(xlEdgeTop)
                            .LineStyle = xlContinuous
                            .ColorIndex = xlAutomatic
                            .TintAndShade = 0
                            .Weight = xlThin
                        End With
                        With lObj.Range(r, c).Borders(xlEdgeBottom)
                            .LineStyle = xlContinuous
                            .ColorIndex = xlAutomatic
                            .TintAndShade = 0
                            .Weight = xlThin
                        End With
                    End If
                End If
            Next x
        Next c
    Next r
End Sub

[scoobster]

Many thanks.
I amended to use the 'borderaround' parameter to save a few links of code;
.BorderAround ColorIndex:=3, Weight:=xlThin