5 Quick Excel VBA Tricks to Boost Your Productivity

If you work with Excel regularly, you know how time-consuming repetitive tasks can be. But with VBA (Visual Basic for Applications), you can automate tasks, save time, and enhance your workflow like never before!

In this post, I’ll share 5 quick and powerful VBA tricks that will help you work smarter in Excel.

Fill an Excel Shape Based on a Percentage

Want to create a dynamic progress bar in Excel? With VBA, you can adjust the fill color of a shape based on a percentage value. This is perfect for tracking project progress, sales goals, or performance metrics.

Example VBA Code:

Private Sub Worksheet_Calculate()

    Dim ws As Worksheet

    Dim shp As Shape

    Dim pct As Double

    ' Set worksheet and shape name

    Set ws = ActiveSheet

    Set shp = ws.Shapes("ProgressBarColor") ' Change this to your shape name

    ' Get percentage from a formula (assumes B7 = achieved, D3 = goal)

    pct = ws.Range("B7").Value / ws.Range("D3").Value

    ' Limit percentage to 100%

    If pct >= 1 Then

        pct = 1

        shp.TextFrame2.TextRange.Characters.Text = "GOAL REACHED"

    Else

        shp.TextFrame2.TextRange.Characters.Text = ""

    End If

    ' Resize shape width based on percentage

    shp.Width = pct * ws.Shapes("ProgressBarOutline").Width ' Adjust max width as needed

    ' Change fill color based on progress

    If pct < 0.5 Then

        shp.Fill.ForeColor.RGB = RGB(255, 0, 0) ' Red for low progress

    ElseIf pct < 0.8 Then

        shp.Fill.ForeColor.RGB = RGB(255, 165, 0) ' Orange for moderate progress

    Else

        shp.Fill.ForeColor.RGB = RGB(0, 200, 0) ' Green for high progress

    End If

End Sub

Automatically Insert Timestamps in a Comment

Need to track when changes are made to specific cells? This VBA trick adds a timestamp in a comment whenever a user modifies a value in Columns A:C.

Example VBA Code:

Public previousValue As String ' Store the old value globally

Private Sub Worksheet_Activate()

    Application.EnableEvents = True

End Sub

' Capture the value before it changes

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count = 1 And Not IsEmpty(Target) Then

        previousValue = Target.Value

    Else

        previousValue = "[Blank]"

    End If

End Sub

' add comment to cell that has been modified

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim newValue As String

    Dim userName As String

    Dim currentComment As String

    ' Ensure a single-cell change

    If Target.Cells.Count > 1 Then Exit Sub

    ' Only track changes in Columns A through C

    If Not Intersect(Target, Me.Range("A:C")) Is Nothing Then

        Application.EnableEvents = False

        userName = Environ("UserName") ' Get system username

        newValue = Target.Value ' Store new value

        ' Construct change log entry

        Dim newEntry As String

        newEntry = "Modified by: " & userName & " on " & Format(Now, "yyyy-mm-dd HH:MM:SS") _

                   & vbNewLine & "From: " & previousValue & vbNewLine & "To: " & newValue

        ' Check if the cell already has a comment

        If Not Target.Comment Is Nothing Then

            currentComment = Target.Comment.Text

            Target.Comment.Delete

            Target.AddComment currentComment & vbNewLine & "-------------------" & vbNewLine & newEntry

        Else

            Target.AddComment newEntry

        End If

        ' Auto-size the comment

        Target.Comment.Shape.TextFrame.AutoSize = True

        Application.EnableEvents = True

    End If

End Sub

Highlight Duplicates Instantly

Duplicate values can cause data errors and reporting issues. Instead of manually scanning for duplicates, this VBA macro automatically highlights them in the current selection.

Example VBA Code:

'Highlight duplicates in active selection

Sub HighlightDuplicates()

    Dim rng As Range

    Dim cell As Range

    Set rng = Selection ' Adjust range as needed

    ' Remove existing highlights

    rng.Interior.ColorIndex = xlNone

    ' Check for duplicates

    For Each cell In rng

        If Application.WorksheetFunction.CountIf(rng, cell.Value) > 1 Then

            cell.Interior.Color = RGB(255, 200, 0) ' Yellow highlight

        End If

    Next cell

End Sub

Send an Outlook Email from Excel

Want to send an email directly from Excel? This VBA script opens Outlook and automatically composes an email with a subject, body, and attachment.

Example VBA Code:

' Send Active Workbook by Email

Sub SendEmail()

    Dim OutlookApp As Object

    Dim Mail As Object

    Set OutlookApp = CreateObject("Outlook.Application")

    Set Mail = OutlookApp.CreateItem(0)

    With Mail

        .To = "recipient@example.com"

        .CC = ""

        .BCC = ""

        .Subject = "Automated Email from Excel"

        .Body = "This is an auto-generated email using VBA!"

        .Attachments.Add ThisWorkbook.FullName ' Attaches current workbook

        .Display ' Use .Send to send immediately

    End With

    Set Mail = Nothing

    Set OutlookApp = Nothing

End Sub

Extract Numbers from a Mixed String

When working with product codes, invoice numbers, or tracking IDs, you may need to extract only the numeric values from a mixed alphanumeric string.

Example VBA Code:

'Highlight duplicates in active selection

Sub HighlightDuplicates()

    Dim rng As Range

    Dim cell As Range

    Set rng = Selection ' Adjust range as needed

    ' Remove existing highlights

    rng.Interior.ColorIndex = xlNone

    ' Check for duplicates

    For Each cell In rng

        If Application.WorksheetFunction.CountIf(rng, cell.Value) > 1 Then

            cell.Interior.Color = RGB(255, 200, 0) ' Yellow highlight

        End If

    Next cell

End Sub

These 5 Excel VBA tricks can save you hours of work and improve your Excel automation skills. Whether you're a beginner or an advanced user, VBA opens up endless possibilities in Excel.

💡 Want more Excel tips?✅ Subscribe to my YouTube Channel for more VBA tutorials! 🎥📊

EXERCISE FILE DOWNLOAD