Count cells if they are a certain colour

[wee wullie]

Hi, can anyone help, i would like to be able to count a number of cells if they are a certain colour.

any help is appreciated.

kind regards

wee wullie

[Klaus]

HI,

please tell us WHAT cells you mean, thanks!


Best

Klaus

[Klaus]

I deleted the duplicate, please only ONE posting per question, thanks!

[wee wullie]

Hi Klaus, i have a table field that uses the following button code to color the cells which contain certain dates, i would like to be able to count the colored cells for each color:

Code: Select all

on mouseUp
   set the useSystemDate to true
   put the internet date into tData
   convert tData to seconds
   
   put 60*60*24 into tSecondsADay
   put 30 * tSecondsADay into t30DaysBefore
   put 0 - t30DaysBefore into t30DaysAfter
   
   put the number of lines of field "TheResultFld" into tSoManyLines
   
   set the itemDelimiter to tab
   put the number of items of line 1 of field "TheResultFld" into tSoManyItems
   
   lock screen
   
   put 1 into tCounter
   repeat with i = 1 to tSoManyLines
      
      repeat with j = 4 to 11  -- only items 4 through 8 will be considered
         
         put item j of line i of field "TheResultFld" into tADate
         if tADate is not a date then next repeat -- test if it is a date else go to next item
         convert tADate to seconds
         put tData - tADate into tDiff
         if tDiff < 0 then
            if tDiff >= t30DaysAfter and tDiff <= 0 then
               set the backgroundColor of item j of line i of field "TheResultFld" to "184,204,228"
            end if
            if tDiff < t30DaysAfter then 
               set the backgroundColor of item j of line i of field "TheResultFld" to "146,208,80"
               end if
         else
            if tDiff >=0 and tDiff < t30DaysBefore then
               set the backgroundColor of item j of line i of field "TheResultFld" to "255,255,128"
            end if
            else
              if tDiff >= t30DaysBefore then
               set the backgroundColor of item j of line i of field "TheResultFld" to "218,150,148"
            end if
         end if
         set the backgroundColor of item 4 of line i of field "TheResultFld" to "white"
         set the backgroundColor of item 6 of line i of field "TheResultFld" to "white"
   set the backgroundColor of item 8 of line i of field "TheResultFld" to "white"
   set the backgroundColor of item 10 of line i of field "TheResultFld" to "white"
      end repeat
   end repeat
     
   
   unlock screen
end mouseUp

kind regards

[Klaus]

Hi,

AHA!

OK, I never worked with the TABLE field, but this should be done with a repeat loop
through all lines and then through all ITEMS (set itemdel to TAB) and check and count
their (items) "backgroundcolor".

Know what I mean?


Best

Klaus

[wee wullie]

No, still cant get it to work, but thanks Klaus, i'll try something else.

[Klaus]

No, still cant get it to work, but thanks Klaus, i'll try something else.

Hm, if you can SET the backgroundcolor, you can also GET the backgroundcolor!
What did you try so far?

[wee wullie]

Yes Klaus, i can SET and GET the bg color like this:

Code: Select all

 set the backgroundColor of item j of line i of field "TheResultFld" to "146,208,80"
               get the backgroundColor of item j of line i of field "TheResultFld"
               put it into tGbg
               answer tGbg

but i cant seem to be able to count how many times that color occurs.

kind regards

[Simon]

Not sure but:

Code: Select all

on mouseUp   
   set the itemDelimiter to tab
   repeat with i = 1 to the number of lines in fld "TheResultFld"
      repeat with j = 1 to the number of items in  line i of fld "TheResultFld" -- I think you have 4-11
         get the backgroundColor of item j of line i of fld "TheResultFld"
         if it = "146,208,80" then 
            add 1 to tGbg
         end if
      end repeat
   end repeat
   answer tGbg
end mouseUp

The new thing I learned was you can't use a "repeat for each line..." because backgroundColor wont be in that, so you must always reference the table. (I should have seen that faster).

Simon

[wee wullie]

Hi Simon, that works perfectly, i use that in a handler that outputs to several fields as totals, and it totals the colored items as expected, however where there are no items to total it leaves the field blank, i tried to use

Code: Select all

if tGbg = 0 then
   put "0" into fld "Gbg1"
   put "0" into fld "Ybg1"
   put "0" into fld "Bbg1"
   put "0" into fld "Rbg1"
   end if

but it only puts zero into the first empty field and leaves the rest blank, i tried an answer dialog to check the variables and strangely the zeros show in the fields while the dialog box is open then they disapear when it closes.

[Klaus]

Hi,

if a variable is empty and not yet used, its content will be its NAME!
Avoid this by "initializing" it like this:

Code: Select all

on mouseUp 

   ## INIT variable HERE:
   put 0 into tGgb
  
   set the itemDelimiter to tab
   repeat with i = 1 to the number of lines in fld "TheResultFld"
      repeat with j = 1 to the number of items in  line i of fld "TheResultFld" -- I think you have 4-11
         get the backgroundColor of item j of line i of fld "TheResultFld"
         if it = "146,208,80" then 
            add 1 to tGbg
         end if
      end repeat
   end repeat
   answer tGbg
end mouseUp

NOW tGgb will be 0 if no colored cells are found!


Best

Klaus

[wee wullie]

Never thought of that, nice 1 Klaus, thanks for that and THANK YOU SIMON, my handler has 16 vars to INIT and i thought there might be a problem but everything works fine, thanks guys.

Kindest Regards

Wullie

[Simon]

Glad to help you Wullie.
Keep asking those questions

Simon