How to Create an Excel VBA Function to Sum Yellow Weekday Hours
Question details
The user needs a custom VBA function to sum the hours in yellow-highlighted cells that contain exactly 8 hours, specifically for Monday to Friday, excluding weekends.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating total working hours from a timesheet where qualifying weekday shifts (8 hours) are highlighted in yellow.
- Observed behavior
- A user-defined VBA function is required to check cell color, cell value, and date to return the total sum of qualifying hours.
Ensure that the Developer tab is enabled in your Excel ribbon so you can access the Visual Basic for Applications (VBA) editor.
Create a Custom VBA Function for Summing Yellow Weekday Hours
Use a custom VBA script to iterate through a range of cells, checking the fill color, cell value, and day of the week before calculating the total.
This user-defined function (UDF) evaluates each cell in a specified range. It checks if the cell's background color is yellow (RGB 255, 255, 0), the value is exactly 8, and the corresponding date in the header row falls on a weekday (Monday to Friday).
Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor window.
In the VBA editor, click on 'Insert' in the top menu and select 'Module' to create a blank workspace for your code.
Copy and paste the following VBA code into the module window: Function SumYellowHours(hoursRange As Range, daysRange As Range) As Long Dim i As Long, total As Long For i = 1 To hoursRange.Cells.Count If hoursRange.Cells(i).Interior.Color = RGB(255, 255, 0) And hoursRange.Cells(i).Value = 8 Then If Weekday(daysRange.Cells(i).Value, vbMonday) <= 5 Then total = total + hoursRange.Cells(i).Value End If End If Next i SumYellowHours = total End Function
Return to your spreadsheet and select the cell where you want the total to appear. Enter the formula =SumYellowHours(B4:H4, B1:H1), adjusting the ranges so the first matches your hours row and the second matches your dates row.
Run VBA Macros Seamlessly in WPS Spreadsheet
WPS Office provides robust support for VBA macros, allowing you to create user-defined functions exactly as you would in Microsoft Excel. Enjoy a powerful, lightweight spreadsheet editor that handles your custom scripts with ease.
- 1. Download and Install WPS Office: Get the latest version of WPS Office from the official website and install it on your computer.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing timesheet or workbook.
- 3. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click 'VBA Editor' to open the coding environment.
- 4. Add and Run Your Code: Insert a new module, paste your custom function code, and use it directly within your WPS Spreadsheet cells.
Frequently Asked Questions
Why isn't my VBA formula updating when I change a cell to yellow?
Spreadsheet software does not automatically recalculate formulas when only the cell formatting (like background color) changes. You must manually force a recalculation by pressing Ctrl+Alt+F9.
How do I modify the VBA code to sum a different color?
In the VBA script, locate the RGB(255, 255, 0) part of the code. Change these RGB values to match the color you want to target (for example, RGB(255, 0, 0) for pure red).
Can I use this custom VBA function in WPS Office?
Yes, WPS Spreadsheet supports VBA. You can open the VBA editor from the Developer tab in WPS Spreadsheet, insert a module, paste the exact same code, and use the function in your workbook.
What happens if a yellow cell contains a value other than 8?
Based on the provided VBA script, the function specifically checks for the value 8. If the yellow cell contains any other number, or is blank, it will be excluded from the total sum.
