Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I calculate number of colored cells?
Hi to master gurus in excel,
I really need your help to solve one issue: how to calculate number of collor cells? For instance, from cells range A2:F2 (6 cells),if there are 2 cells filled in any colors, the counter in cell G2 should be equal to 4(6-2). Any thoughts? -- Thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I calculate number of colored cells?
Sub countcoloredcells()
For Each C In Range("a2:f2") If C.Interior.ColorIndex = -4142 Then mc = mc + 1 Next C MsgBox mc End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Rui" wrote in message ... Hi to master gurus in excel, I really need your help to solve one issue: how to calculate number of collor cells? For instance, from cells range A2:F2 (6 cells),if there are 2 cells filled in any colors, the counter in cell G2 should be equal to 4(6-2). Any thoughts? -- Thanks in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I calculate number of colored cells?
Select your range and this will display the number of cells without color:
Sub ColorMeElmo() Dim r As Range, Kount As Long Kount = 0 For Each r In Selection If r.Interior.ColorIndex = xlNone Then Kount = Kount + 1 End If Next MsgBox Kount End Sub -- Gary''s Student - gsnu200909 "Rui" wrote: Hi to master gurus in excel, I really need your help to solve one issue: how to calculate number of collor cells? For instance, from cells range A2:F2 (6 cells),if there are 2 cells filled in any colors, the counter in cell G2 should be equal to 4(6-2). Any thoughts? -- Thanks in advance! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I calculate number of colored cells?
you can use a udf for this:
Sub test_colorcount() MsgBox colorcount(Range("I2"), Range("H6:J15")) End Sub Function colorcount(base As Range, target As Range) As Long Dim cell As Range Dim count As Long For Each cell In target.Cells If cell.Interior.Color = base.Interior.Color Then count = count + 1 Next colorcount = count End Function color cell I1 (for my example) say yellow. Put a border arounf H6:J15 so you can see it clearly, then color a few cell with the same fill as I2. in another cell put =colorcount(I2,H6:J15) "Rui" wrote in message ... Hi to master gurus in excel, I really need your help to solve one issue: how to calculate number of collor cells? For instance, from cells range A2:F2 (6 cells),if there are 2 cells filled in any colors, the counter in cell G2 should be equal to 4(6-2). Any thoughts? -- Thanks in advance! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I calculate number of colored cells?
This is pretty much what Gary did, but I tweaked it a bit. This will give
you the total in the next cell under your range. I'm not sure how you get your range, but you can select it likes Gary's code or if the range is always positioned in the same address you can use mine. Option Explicit Sub NoColorCellCounter() Dim MyRange As Range Dim d As Range Dim i As Long MyRange = Range("A1:A10") i = 0 For Each c In MyRange If c.Interior.ColorIndex = xlNone Then i = i + 1 Next c MyRange.Offset(1).Value = i End Sub Hope this helps! If so, click "YES" below. -- Cheers, Ryan "Rui" wrote: Hi to master gurus in excel, I really need your help to solve one issue: how to calculate number of collor cells? For instance, from cells range A2:F2 (6 cells),if there are 2 cells filled in any colors, the counter in cell G2 should be equal to 4(6-2). Any thoughts? -- Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count the number of colored cells in a column | Excel Worksheet Functions | |||
Calculate number of weeks between two cells | Excel Discussion (Misc queries) | |||
Is there a way to count the number of different colored cells? | Excel Worksheet Functions | |||
Excel - formula to calculate colored fill cells within a range wi. | Excel Worksheet Functions | |||
Cell right next to colored cells is automatically colored on entering a value | Excel Programming |