excel - Limiting Target to a Range of Noncontiguous Cells -

i have formatted worksheet allows me to:

• double-click on blank cell , background color turns red , gives value of "not recvd".
• double-click again , turns orange "partial" value.
• double-click again , turns green "recvd" value.
• double-click again , turns blue "na" value.
• double-click again , goes blank.

i able accomplish of tutorials , online searches. want add couple more features spreadsheet haven't been able find/figure these out. features need inserted existing code are:

1. assigning specific cell/range (as opposed every cell on worksheet) ability of changing color/value double-click described above. there 120 cells need designate such.
2. assuming none of cells blank, need insert equation calculates percentage of 120 cells not blue/"na" red/"not recvd"; orange/"partial"; , green/"recvd". these percentages located on same worksheet , know how designate specific cells/range well.

i this:

not recvd  15% partial    20% recvd      65% 

here existing code have far:

private sub worksheet_beforedoubleclick(byval target excel.range, cancel boolean)      if target.interior.colorindex = xlnone         target.value = " "         target.interior.colorindex = 3         target.value = "not recvd"     elseif target.interior.colorindex = 3         target.interior.colorindex = 45         target.value = "partial"     elseif target.interior.colorindex = 45         target.interior.colorindex = 4         target.value = "recvd"     elseif target.interior.colorindex = 4         target.interior.colorindex = 41         target.value = "n/a"     elseif target.interior.colorindex = 41         target.interior.colorindex = xlnone         target.value = " "     end if      cancel = true end sub  

i using windows xp , excel 2003.

i had repair few typos in cell range identified repeating pattern , broke 2 strings referencing rows , columns. put declarations area (top of worksheet code sheet) private constant strings.

private const pciseccols string = "h:h,k:k,n:n,q:q,t:t,w:w" private const pcisecrows string = "6:7,10:12,15:17,20:24,27:27,30:30,33:33,36:36,39:39,42:42,45:45,48:48,51:51,54:54,57:57" 

these used define range objects within intersect (intersect method) call see if double-clicked cell should included. when ran intersect(range(pciseccols), range(pcisecrows)).interior.colorindex = 10 came this.

            

while comes 144 cells instead of 120, best guess have provided needs done add cell double-clicking intersect function achieve dc-filtered response.

while found there ambiguity description of 'percentages located @ aa22,aa23,aa24', have assumed percentages intended in aa22:aa24 , legend descriptions identifying each percentage intended z22:z24.

option explicit  private const pciseccols string = "h:h,k:k,n:n,q:q,t:t,w:w" private const pcisecrows string = "6:7,10:12,15:17,20:24,27:27,30:30,33:33,36:36,39:39,42:42,45:45,48:48,51:51,54:54,57:57"  private sub worksheet_beforedoubleclick(byval target range, cancel boolean)     if not intersect(target, range(pciseccols), range(pcisecrows)) nothing         on error goto fìn         cancel = true         application.enableevents = false         dim icnt long, rpct range         'debug.print intersect(range(pciseccols), range(pcisecrows)).count         'debug.print intersect(range(pciseccols), range(pcisecrows)).address         select case target.interior.colorindex             case xlnone                 target.interior.colorindex = 3                 target.value = "not recvd"             case 3                 target.interior.colorindex = 45                 target.value = "partial"             case 45                 target.interior.colorindex = 4                 target.value = "recvd"             case 4                 target.interior.colorindex = 41                 target.value = "n/a"             case 41                 target.interior.colorindex = xlnone                 target.value = vbnullstring             case else                 target.interior.pattern = xlnone                 target.value = vbnullstring         end select          range("aa22:aa24") = 0         intersect(range(pciseccols), range(pcisecrows))             icnt = .count             debug.print icnt             each rpct in .cells                 select case rpct.interior.colorindex                     case 3                         range("aa22") = range("aa22").value2 + 1                     case 45                         range("aa23") = range("aa23").value2 + 1                     case 4                         range("aa24") = range("aa24").value2 + 1                     case 41                         icnt = icnt - 1                     case else                         'do nothing                 end select             next rpct             debug.print icnt             range("aa22") = range("aa22").value2 / icnt             range("aa23") = range("aa23").value2 / icnt             range("aa24") = range("aa24").value2 / icnt         end     end if fìn:     application.enableevents = true end sub 

that should of it. count of cells in noncontiguous range decremented each blue cell , percentage retrieved using count opposed of cells.

if wish use actual worksheet function wider percentages, can use create countif formula on whole noncontiguous range. using discontinuous range countif difficult not impossible. formula aa2 be,

    =sum(countif(indirect({"h6:h7","h10:h12","h15:h17","h20:h24","h27","h30","h33","h36","h39","h42","h45","h48","h51","h54","h57","k6:k7","k10:k12","k15:k17","k20:k24","k27","k30","k33","k36","k39","k42","k45","k48","k51","k54","k57","q6:q7","q10:q12","q15:q17","q20:q24","q27","q30","q33","q36","q39","q42","q45","q48","q51","q54","q57","n6:n7","n10:n12","n15:n17","n20:n24","n27","n30","n33","n36","n39","n42","n45","n48","n51","n54","n57","t6:t7","t10:t12","t15:t17","t20:t24","t27","t30","t33","t36","t39","t42","t45","t48","t51","t54","t57","w6:w7","w10:w12","w15:w17","w20:w24","w27","w30","w33","w36","w39","w42","w45","w48","w51","w54","w57"}), z22))/144 

format percentage , fill down aa24.