Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printer not picking up colours from conditional formatting. | Excel Discussion (Misc queries) | |||
Conditional Formatting / Cell Fill Colours | New Users to Excel | |||
how do i get more than 3 colours in conditional formatting | Excel Discussion (Misc queries) | |||
colours missing in conditional formatting. | Excel Worksheet Functions | |||
Conditional Formatting with Colours / Colors | Excel Worksheet Functions |