Ask Your Question

Create loop in range cell for count

asked 2017-12-11 10:26:54 +0200

ypkdani gravatar image

updated 2017-12-11 17:22:34 +0200

Lupp gravatar image


i need to count the cell with the same background color in a range of cell. The range is define as :

oRange = sheet.getCellRangeByPosition(7, nRiga+startIndex, 8, nRiga+stopIndex)

and in this range i use a condition

oRed4Condition(0).Name = "Operator"
oRed4Condition(0).Value =
oRed4Condition(1).Name = "Formula1"
oRed4Condition(1).Value = climit
oRed4Condition(2).Name = "Formula2"
oRed4Condition(2).Value = "-" + climit
oRed4Condition(3).Name = "StyleName"
oRed4Condition(3).Value = "Senza nome1"

I want to count the cells that are true for this condition. i'm tring with

for each c In oRange.CellFormatRanges
  if (c.CellBackColor =     oRed4Condition(3).Value) then
    checkResult = checkResult + 1
next c

but oRange.CellFormatRanges is not correct... how i can loop all cell and check this condition?

Thanks Daniele
(Edited for better readability by @Lupp )

edit retag flag offensive close merge delete


Styles used in conditional formatting are only applied for the view and never assigned to the cells. Therefore you cannot find the respective cells testing for properties of such a style. You need to evaluate the condition as @JohnSUN demonstrated.

Lupp gravatar imageLupp ( 2017-12-11 17:31:40 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2017-12-11 10:45:54 +0200

JohnSUN gravatar image

updated 2017-12-11 10:47:12 +0200

Do you mean this?

for each c In oRange
    if (c.Value < -climit) or (c.Value > climit) then
        checkResult = checkResult + 1
next c
edit flag offensive delete link more

answered 2017-12-11 18:05:30 +0200

Lupp gravatar image

updated 2017-12-11 18:06:08 +0200

You (the OQ) should consider to get the result using COUNTIF().
like in
{=COUNTIF(ABS(H12:I32);">"& 2)} e.g. or, now parametrized:
{=COUNTIF(ABS(OFFSET(INDIRECT("A1");nRiga+startIndex;7;startIndex-stopIndex+1;8-7+1));">"& cLimit2)}

edit flag offensive delete link more

answered 2017-12-13 10:54:02 +0200

ypkdani gravatar image

Thanks to all!!

edit flag offensive delete link more


This is not an answer, so please delete it and add a comment under the question instead. Also, if one of the answers is helpful then please click ✔ to mark it as correct.

Jim K gravatar imageJim K ( 2017-12-13 18:24:03 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-12-11 10:26:54 +0200

Seen: 836 times

Last updated: Dec 13 '17