Running macros in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office assistant managing monthly sales data.

📋 Request: Your manager wants you to automate the process of formatting the sales report to save time each month.

📊 Data: You have a sales data table with columns: Date, Region, Salesperson, Product, Units Sold, and Total Sales.

🎯 Deliverable: Create and run a macro that formats the sales report by applying bold headers, setting column widths, and adding currency format to Total Sales.

Sample Data

Date	Region	Salesperson	Product	Units Sold	Total Sales
2024-05-01	North	Alice	Widget A	10	500
2024-05-02	South	Bob	Widget B	5	250
2024-05-03	East	Charlie	Widget A	8	400
2024-05-04	West	Diana	Widget C	12	720
2024-05-05	North	Eva	Widget B	7	350
2024-05-06	South	Frank	Widget C	9	540
2024-05-07	East	Grace	Widget A	6	300
2024-05-08	West	Hank	Widget B	11	550

1

Step 1: Open the Excel workbook with the sales data.

Expected Result

Sales data table is visible with columns Date, Region, Salesperson, Product, Units Sold, Total Sales.

2

Step 2: Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

Expected Result

VBA editor window opens.

3

Step 3: In the VBA editor, click Insert > Module to add a new module.

Expected Result

A new blank module appears for code entry.

4

Step 4: Type the following macro code to format the sales report:

Sub FormatSalesReport() With Sheets("Sheet1") .Rows(1).Font.Bold = True .Columns("A:F").AutoFit .Columns("F").NumberFormat = "$#,##0.00" End With End Sub

Expected Result

Macro code is entered correctly in the module.

5

Step 5: Close the VBA editor and return to Excel.

Expected Result

Back in Excel with the workbook active.

6

Step 6: Press Alt + F8 to open the Macro dialog box, select 'FormatSalesReport', and click Run.

Expected Result

The first row headers become bold, columns adjust width to fit content, and Total Sales column shows currency format.

Final Result

Date       | Region | Salesperson | Product  | Units Sold | Total Sales
-----------------------------------------------------------------------
2024-05-01 | North  | Alice       | Widget A | 10         | $500.00
2024-05-02 | South  | Bob         | Widget B | 5          | $250.00
2024-05-03 | East   | Charlie     | Widget A | 8          | $400.00
2024-05-04 | West   | Diana       | Widget C | 12         | $720.00
2024-05-05 | North  | Eva         | Widget B | 7          | $350.00
2024-05-06 | South  | Frank       | Widget C | 9          | $540.00
2024-05-07 | East   | Grace       | Widget A | 6          | $300.00
2024-05-08 | West   | Hank        | Widget B | 11         | $550.00

✓Headers are bold for easy reading.

✓Columns are adjusted to fit the data neatly.

✓Total Sales column shows values as currency for clarity.

Bonus Challenge

Modify the macro to also add a light yellow background color to the header row.

Show Hint

Use .Rows(1).Interior.Color = RGB(255, 255, 153) inside the With block.