Forum Discussion
Help with VBA Functions
You can achieve this with a custom VBA function. Here's a VBA code that creates a custom function to implement the logic you described:
Vba code is untested, please backup your file first.
Function SumYellowHours(hoursRange As Range, daysRange As Range) As Integer
Dim cell As Range
Dim totalHours As Integer
Dim i As Integer
totalHours = 0
' Loop through each cell in the range
For i = 1 To hoursRange.Cells.Count
Set cell = hoursRange.Cells(i)
' Check if the cell is yellow and contains 8
If cell.Interior.Color = RGB(255, 255, 0) And cell.Value = 8 Then
' Check if the corresponding day is Monday-Friday
If Weekday(daysRange.Cells(i).Value, vbMonday) <= 5 Then
' Add hours to total
totalHours = totalHours + cell.Value
End If
End If
Next i
SumYellowHours = totalHours
End FunctionTo use this function, follow these steps:
- Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
- Go to Insert > Module to insert a new module.
- Copy and paste the above VBA code into the module window.
- Close the VBA editor.
- In your Excel worksheet, enter the formula =SumYellowHours(B4:H4, B1:H1) in the cell where you want the result to appear.
- Press Enter to calculate the result.
This function will loop through each cell in the specified range (B4:H4 for hours and B1:H1 for days), check if the cell is yellow and contains 8, and if the corresponding day is Monday-Friday. If all conditions are met, it adds the hours to the total. Finally, it returns the total hours as the result of the function.Formularbeginn The text, steps and code were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.