Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Colours
Hi, I have a group of cells, the colour of the cell is dependant on it's
value being either higher or lower than 'x'. The problem I have is that 'x' isn't constant throughout the cells. Example: A1 = higher or lower than 10 A2 = higher or lower than 7 A3 = higher or lower than 25 etc. These aren't the true values, just examples. Higher than 'x' the cell is green, lower and it's red. What formula, if any, do I put into another cell that can count the green or red cells in a given range? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Colours
Hi,
Alt+F11 to open Vb editor, right click 'this workbook' and insert module and paste the code below in. Call with =sumbycolor(A1:A20,B1) Where A1:a20 is the range to count and B1 is the colour you want to count Function SumByColor(Rng As Range, ClrRange As Range) As Double Dim c As Range, TempSum As Double, ColorIndex As Integer Application.Volatile ColorIndex = ClrRange.Interior.ColorIndex TempSum = 0 On Error Resume Next For Each c In Rng If c.Interior.ColorIndex = ColorIndex Then TempSum = TempSum + 1 End If Next c On Error GoTo 0 Set c = Nothing SumByColor = TempSum End Function Mike "Darren" wrote: Hi, I have a group of cells, the colour of the cell is dependant on it's value being either higher or lower than 'x'. The problem I have is that 'x' isn't constant throughout the cells. Example: A1 = higher or lower than 10 A2 = higher or lower than 7 A3 = higher or lower than 25 etc. These aren't the true values, just examples. Higher than 'x' the cell is green, lower and it's red. What formula, if any, do I put into another cell that can count the green or red cells in a given range? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Colours
BTW this won't work with conditional formatted colours. That can be done but
it's more difficult. Mike "Darren" wrote: Hi, I have a group of cells, the colour of the cell is dependant on it's value being either higher or lower than 'x'. The problem I have is that 'x' isn't constant throughout the cells. Example: A1 = higher or lower than 10 A2 = higher or lower than 7 A3 = higher or lower than 25 etc. These aren't the true values, just examples. Higher than 'x' the cell is green, lower and it's red. What formula, if any, do I put into another cell that can count the green or red cells in a given range? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Colours
Hi Darren
Take it the other way round, ie: How many cells have a value higher or lower than 10 How many cells have a value higher or lower than 7 ....and so on See help on COUNTIF or SUMPRODUCT functions. HTH Cordially Pascal "Darren" a écrit dans le message de news: ... Hi, I have a group of cells, the colour of the cell is dependant on it's value being either higher or lower than 'x'. The problem I have is that 'x' isn't constant throughout the cells. Example: A1 = higher or lower than 10 A2 = higher or lower than 7 A3 = higher or lower than 25 etc. These aren't the true values, just examples. Higher than 'x' the cell is green, lower and it's red. What formula, if any, do I put into another cell that can count the green or red cells in a given range? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Colours in Excel | New Users to Excel | |||
Counting cell colours | Excel Worksheet Functions | |||
Counting colours | Excel Worksheet Functions | |||
Counting colours | Excel Discussion (Misc queries) | |||
Counting Colours | Excel Programming |