Data Validation and macro enabled workbook

[titantough]

Hi there,

Pulling my hair out to understand why i cannot save a workbook from .xlsx to .xlsm and carry over my data validations from an outside database workbook? I have tried clearing my data validation and entering it in again, but i receive an error. I have tried naming the cell range in the database workbook, creating a name in the .xmls workbook, and using the range name in the data validation, but again an error. This time it says the name range is not found in the other workbook.

The database file is saved as .xlsm, and the original workbook that is .xlsx. I wanted to create a few VBA codes, so i needed to save it now as a .xlsm, but i am at my wits end.

I have attached two files:

Database file is Master Material List.xlsm
Workbook is test takeoff.xlsx

In the workbook, I sometimes want to copy and paste a block, for instance rows 2:6. You will note that there are subtotals found in in cell O6, P6, Q6 and S6. When i copy and paste the block, I want the sum of the subtotals to be added automatically at the bottom Totals row. This is currenty O20, P20, Q20 and S20. To do this easily, i tried to use VBA colorfunction code to add all the like color cells. The code worked great, but then when i wanted to save the file as a .xlsm file to keep my codes, upon reopening the file, it would not read my Master Materil List, which is my data validation in column A, following with lookup values throughout the worksheet. Right now my test takeoff file does not have VBA behind it, therefore my totals row is simply based on the sum function.

Here is my colorfunction VBA code i was trying to use, and then changed my totals cells to read, for example in O2 =ColorFunction($O$6,O1:O17,TRUE).

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

    Dim rCell As Range

    Dim lCol As Long

    Dim vResult
    
lCol = rColor.Interior.ColorIndex



    If SUM = True Then

        For Each rCell In rRange

            If rCell.Interior.ColorIndex = lCol Then

                vResult = WorksheetFunction.SUM(rCell, vResult)

            End If

        Next rCell

    Else

        For Each rCell In rRange

            If rCell.Interior.ColorIndex = lCol Then

                vResult = 1 + vResult

            End If

        Next rCell

    End If



   ColorFunction = vResult

End Function

If anyone can offer suggestion, i would greatly appreciate it.

Thanks,