Add Cell border for active cells

How to add cell Borders, only in active cells before convert excel into pdf file as shown in below


Can anyone help me!?

Hi @jai_kumar2

Install BalaReva Easy Excel Activities → manage package

Have a look the video

Regards
Gokul

Hello @jai_kumar2
Try this method
1. Use excel Application Scope
2. Drag and drop the send hot key
3. SET ctrl+A
4.Another Send hot key activity
5.Set “Shift+Alt+H+B+A”

you can get results

hi @Gokul_Jayakumar
thanks for your reply

But this is not dynamic way
if you see the picture ,there only active cells are highlighted with border lines

@jai_kumar2 Got it. Then you have to look the video and set boder for each set of tables seperately.

If you have Excel installed and can execute VBA, you can use this macro in an Invoke VBA activity. It will work dynamically, just pass it your sheet name as a parameter.

Save this script in your project directory, for example in a text file named vba.txt.

Function ApplyCellBorderActiveCells(sheetName As String) As String
    On Error GoTo ErrorHandler
    For Each cell In Sheets(sheetName).UsedRange
        If cell.Text = vbNullString Then GoTo SkipCell
        With cell.Borders
            .LineStyle = xlContinuous
            .Color = xlBlack
        End With
SkipCell:
    Next cell
    ApplyCellBorderActiveCells = "Success"
    Exit Function
ErrorHandler:
    Debug.Print Err.Description
    ApplyCellBorderActiveCells = "Error: " & Err.Description
End Function


Example usage:

Result:
image

You may need to enable access to VBA if this is your first time doing it. If so,

  1. Open Excel and go into File > Options
  2. Click the Trust Centre tab
  3. Click Trust Centre Settings…
  4. Click the Macro Settings tab
  5. Check the box for Trust access to the VBA project object model
  6. Click OK
1 Like

Hi Moosh,

Its Working

Thanks for the solution

Hi moosh

can we add one or more sheets names as a parameter?

can you please help me on this

Sure, amend your script to this:

Function ApplyCellBorderActiveCells(ParamArray shts() As Variant) As String
    On Error GoTo ErrorHandler
    For Each sht In shts
        For Each cell In sheets(sht).UsedRange
            If cell.Text = vbNullString Then GoTo SkipCell
            With cell.Borders
                .LineStyle = xlContinuous
                .Color = xlBlack
            End With
SkipCell:
        Next cell
    Next sht
    ApplyCellBorderActiveCells = "Success"
    Exit Function
ErrorHandler:
    Debug.Print Err.Description
    ApplyCellBorderActiveCells = "Error: " & Err.Description
End Function

And pass in multiple sheets like so:
image

Hi moosh,

Thanks for your reply

but still some merged cells are left blank

Do i need to change some thing in script to make it on merged cells also

Yup, use this one for merged cells too

Function ApplyCellBorderActiveCells(ParamArray shts() As Variant) As String
    On Error GoTo ErrorHandler
    Dim rng, borderRng As Range
    For Each sht In shts
        For Each cell In Sheets(sht).UsedRange
            If cell.Text = vbNullString Then GoTo SkipCell
            If cell.MergeCells Then
                Set rng = cell.MergeArea
            Else
                Set rng = cell
            End If
            If borderRng Is Nothing Then
                Set borderRng = rng
            Else
                Set borderRng = Union(borderRng, rng)
            End If
SkipCell:
        Next cell
        With borderRng.Cells
            .Borders.LineStyle = xlContinuous
            .Borders.Color = xlBlack
        End With
        Set borderRng = Nothing
    Next sht
    ApplyCellBorderActiveCells = "Success"
    Exit Function
ErrorHandler:
    Debug.Print Err.Description
    ApplyCellBorderActiveCells = "Error: " & Err.Description
End Function
1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.