Conditional Formatting Counting Colours
 

I currently have a spreadsheet that has conditional formatting applied to its
rows. This conidtional formatting works by looking at the values in cells.

I would like to be able to count the colours within the conditional
formatting, ie

Red = 100
Orange = 50
Green = 360

I cannot use countif becuase the conidtional formatting (Format-Conditional
Formatting) uses a complex formulas, how can i do this using VBA.

Thanks

Conditional Formatting Counting Colours
 

The simplest way is to count using the condition that you have in CF.

For instance,

="Num reds = " & COUNTIF(A1:A100,100)

Counting CF colours is convoluted.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DMc2004" wrote in message
...
I currently have a spreadsheet that has conditional formatting applied to
its
rows. This conidtional formatting works by looking at the values in cells.

I would like to be able to count the colours within the conditional
formatting, ie

Red = 100
Orange = 50
Green = 360

I cannot use countif becuase the conidtional formatting
(Format-Conditional
Formatting) uses a complex formulas, how can i do this using VBA.

Thanks

Conditional Formatting Counting Colours
 

Sorry, just saw the part about not using COUNTIF. You can usually get around
this by using SUMPRODUCT

As an example

=SUMPRODUCT(--(A1:A1000=1000),--(Month(B1:B1000=1),--(LEFT(C1:C1000,1)="A"))

which counts all items that are equal to 1000 in A, the month in B is Jan,
and column C starts with A. If you can do the condition in CF, you can count
it (he says positively :-))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
The simplest way is to count using the condition that you have in CF.

For instance,

="Num reds = " & COUNTIF(A1:A100,100)

Counting CF colours is convoluted.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DMc2004" wrote in message
...
I currently have a spreadsheet that has conditional formatting applied
to
its
rows. This conidtional formatting works by looking at the values in
cells.

I would like to be able to count the colours within the conditional
formatting, ie

Red = 100
Orange = 50
Green = 360

I cannot use countif becuase the conidtional formatting
(Format-Conditional
Formatting) uses a complex formulas, how can i do this using VBA.

Thanks

Conditional Formatting Counting Colours
 

Unfortunately, the background color isn't visible to code or functions. The
best solution is to add the formatting conditions to code.
Simple really as you can only have three conditions anyway
Add a UDF (User Defined Function) that returns say a 1,2 or 3 dependign on
the result, then you can easily count the 1's, 2's and 3's


PUBLIC FUNCTION MyConditions(CellValue as range)
SELECT CASE TRUE
CASE condition1
MyConditions=1
CASE condition2
MyConditions=2
CASE condition3
MyConditions=3
CASE ELSE
MyConditions=0
END SELECT
END FUNCTION

HT
Parick Molloy









"DMc2004" wrote:

I currently have a spreadsheet that has conditional formatting applied to its
rows. This conidtional formatting works by looking at the values in cells.

I would like to be able to count the colours within the conditional
formatting, ie

Red = 100
Orange = 50
Green = 360

I cannot use countif becuase the conidtional formatting (Format-Conditional
Formatting) uses a complex formulas, how can i do this using VBA.

Thanks