Need Help with Name Manager

I'm guessing you tried to use one of the old Excel 4 macro functions in a range name. These mandate you save the workbook as a workbook with macros (xlsm or xlsb format), otherwise they will not be saved with the workbook.

Take this as an alternative:

1. Use a Helper Column Instead of Color

Instead of relying on cell color, add a column where users select the department from a dropdown (e.g., “Sales”, “HR”, “Finance”). Then use formulas like COUNTIF or SUMIF to track time.

=SUMIF(B2:B100, "Sales", C2:C100)

Where:

• Column B = Department
• Column C = Time spent

This is 100% portable, doesn’t rely on macros, and works across all versions of Excel.

2. Use Conditional Formatting for Visuals

You can still color-code cells using Conditional Formatting based on department names. This keeps the visual appeal without tying logic to cell color.

3. If You Must Use Color, Use VBA Carefully

If you still want to count by color, here’s a simple VBA function you can include in your workbook:

Function CountColor(rng As Range, colorCell As Range) As Long
    Dim cell As Range
    Dim count As Long
    count = 0
    For Each cell In rng
        If cell.Interior.Color = colorCell.Interior.Color Then
            count = count + 1
        End If
    Next cell
    CountColor = count
End Function

Then use it like:

=CountColor(A2:A100, A1)

Where A1 has the color you want to count.