Help with VBA Functions

Question

I am using excel for this Data looks like this&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Monday&nbsp; &nbsp; Tuesday&nbsp; &nbsp; Wednesday&nbsp; &nbsp; Thursday&nbsp; &nbsp; Friday&nbsp; &nbsp; Staurday&nbsp; &nbsp; Sunday&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/04/24&nbsp; &nbsp; 6/05/24&nbsp; &nbsp; &nbsp; &nbsp;6/06/24&nbsp; &nbsp; &nbsp; &nbsp; 6/07/24&nbsp; &nbsp; 6/08/24&nbsp; &nbsp;6/09/24&nbsp; &nbsp; 6/10/24Zachary&nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8Nipun&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0Juno&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12Ankita&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12I want to create a function using VBA.The function should like this=FunctionName(B4:H4,B1:H1)So I want the function to first check the cells and see if any of them are yellow in color RGB(255,255,0) and have 8 written inside the cell. Than for those cells check the corresponding days names. If its Saturday or Sunday than ignore. But if its any of the day from Monday-Friday than I want it to add the hours for the whole week. So for Juno the output should be 32. and for the rest the output should be 0.

nikolinode · Answer

Saboor16&nbsp;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) &lt;= 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 &gt; 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.&nbsp;&nbsp;My answers are voluntary and without guarantee!&nbsp;Hope this will help you.Was the answer useful? Mark as best response and Like it!This will help all forum participants.

Forum Discussion

Help with VBA Functions

1 Reply

Resources