1

In a range of cells, is there any way of counting the ones with certain background color?

1

Here is an example in AppleScript that will count the number of cells in a range that has the background color of red.

Numbers Image

Note that the target Numbers document was open and in the background of Script Editor.

When the script was run, it displayed the following dialog box.

Display Dialog Image

--  # User definded variables.

set theRange to "A1:L10"

--  # The background color of the range’s cells. Expressed as a list of RGB (Red, Green, Blue) 
--  #  values, from 0 to 65535. For example, the color red is: {65535, 0, 0}

set R to 65535
set G to 0
set B to 0


--  # Other variables.

set thisColor to ""
set theRGBValue to {}
set theCount to 0


tell application "Numbers"
    tell document 1
        tell sheet 1
            tell table 1
                tell range theRange
                    repeat with i from 1 to (cell count)
                        set thisColor to background color of cell i as string
                        if thisColor is not "" then
                            set theRGBValue to (background color of cell i)
                            if item 1 of theRGBValue is equal to R and item 2 of theRGBValue is equal to G and item 3 of theRGBValue is equal to B then
                                set theCount to theCount + 1
                            end if
                        end if
                    end repeat
                end tell
            end tell
        end tell
    end tell
end tell

display dialog "The cell count with the color {" & R & ", " & G & ", " & B & "} is: " & theCount buttons {"OK"} default button 1

Note: This was tested on an old version of Numbers (09 ver 2.3) and may need to be adjusted for newer versions.

If you do not know what the RGB value of a given cell is, to plug into the script above, then to get the RGB value of the background color of a given cell, e.g. F5, use:

tell application "Numbers"
    get background color of cell 1 of range "F5:F5" of table 1 of sheet 1 of document 1
end tell

Just an FYI, I coded the main example further above in the long form and it could be condensed down to the following for the tell application "Numbers" block:

tell application "Numbers"
    tell range theRange of table 1 of sheet 1 of document 1
        repeat with i from 1 to (cell count)
            if (background color of cell i as string) is not "" then
                set theRGBValue to (background color of cell i)
                if item 1 of theRGBValue is equal to R and item 2 of theRGBValue is equal to G and item 3 of theRGBValue is equal to B then
                    set theCount to theCount + 1
                end if
            end if
        end repeat
    end tell
end tell

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .