Adding/Summing Non-Highlighted Cells From Multiple Worksheets

[maszap]

Hi Everyone,

I'm wondering if I could receive some assistance in modifying a VBA Code that will add/sum the same non-highlighted cells from all the spreadsheets in a workbook.

In other words, all A1 cells will be added/summed if they are not highlighted, all A2 cells will be added/summed if they are not highlighted, etc.

Process:
Each week I add a tab to a workbook which contains that week's order spreadsheet.
The spreadsheets are identical in formatting.
The individual tab names are the dates on which I have placed the orders.
As I receive items, I highlight the cell(s) on the corresponding worksheet.
I have a dedicated sheet [Sheet(1)] which is also formatted identically to the other spreadsheets, but I would like Sheet(1) to add/sum all of the other spreadsheet(s) non-highlighted sister cells (All A1's for instance) so I know how many of a particular item are still to be received.

I am using the ADDACROSSSHEETS function which I found on another site, is listed below, and does add/sum the values of all A1 cells. I just need the code to be modified so that it only adds non-highlighted cells.

Any assistance will be greatly appreciated. Thank you.


Function ADDACROSSSHEETS(rng As Range) As Variant

valRow = rng.Row
valCol = rng.Column
Application.Volatile

For x = 2 To Sheets.Count
ADDACROSSSHEETS = Sheets(x).Cells(valRow, valCol).Value + ADDACROSSSHEETS
Next x

End Function

 

[kevin9999]

I'm assuming the highlighting you're talking about is Interior cell colouring and that it's what you add yourself (not CF). See if this works for you:

Function ADDACROSSSHEETS(rng As Range) As Variant
    valRow = rng.Row
    valCol = rng.Column
    Application.Volatile
    For x = 2 To Sheets.Count
        If Sheets(x).Cells(valRow, valCol).Interior.ColorIndex = xlNone Then
            ADDACROSSSHEETS = Sheets(x).Cells(valRow, valCol).Value + ADDACROSSSHEETS
        End If
    Next x
End Function

 

[maszap]

Amazing. This is perfect for me. Thank you so much for your help and thank you for sharing your skills, they are very impressive.

 

[kevin9999]

Amazing. This is perfect for me. Thank you so much for your help and thank you for sharing your skills, they are very impressive.

Happy to help, and thanks for the feedback