Home |
Search |
Today's Posts |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Paul,
You spotted the problem correct.. Awesome.. I was using a colorindex 6 i.e. yellow. I was incorrect in my part. Thanks for spotting that out. Now the code works perfect.. Great. Regards, Thulasiram PCLIVE (RemoveThis) wrote: Thulasiram Are you sure the color index for those cells is "3"? I used code to set the color index when I tested it. For example, in cells G1:I1 I entered 200, 100 and 340. Then I selected (Highlighted) cells "G1:I1". Then to ensure I was using a color index of 3, I added the following code to the top of the code I provided. Selection.Interior.ColorIndex = 3 I steped through the code using F8. It counted the 3 cells that were colored and added the values of each, totalling 640. It seems to worked on my side. Regards, Paul "Thulasiram" wrote in message ups.com... Paul, Thanks for that code. Along with your code, I added the following lines to display the result Range("A2").Value = c Range("A3").Value = sm Output was 0 and 0. I dont know why this happens.. Any idea? Please clarify. Thanks, Thulasiram PCLIVE (RemoveThis) wrote: One way: Sub test() Dim rng As Range c = 0 sm = 0 Set rng = Range("A1:Z1") For Each cell In rng If cell.Interior.ColorIndex = 3 _ Then c = c + 1 sm = sm + cell.Value Else: End If Next cell End Sub Regards, Paul "Sjakkie" wrote in message ... here is a function i found a while back Function Count_By_Color(rColor As Range, rCountRange As Range) Dim rCell As Range Dim iCol As Integer Dim vResult iCol = rColor.Interior.ColorIndex For Each rCell In rCountRange If rCell.Interior.ColorIndex = iCol Then vResult = vResult + 1 End If Next rCell Count_By_Color = vResult End Function "Thulasiram" wrote: Hello All, I have a question on counting and sum the cells in a range that has color/conditional formatting. For example, I have Dim rng as Range rng = Range ("A1:Z1") Assuming that cells B1 (value = 100), D1 (value = 200), F1(value = 340) have color formatting (colorindex = 3) in the 'rng', the number of highlighted cells is 3. the sum of the highlighted celss = 100+200+340 = 640. Please help me translate into a VBA code. For each cell in rng if cell.interior.colorindex = 3 then count = "number of highlighted cells in the range" ---- how to translate this line into a VBA code? 'for this case, the output would be count = 3 sum = "the sum of the values in the highlighted cells" ---- how to translate this line into a VBA code? 'for this case, the output would be sum = 640 end if next cell Thanks for your help, -Thulasiram |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Can you count cells by color? | Excel Worksheet Functions | |||
HOW DO I COUNT CELLS OF A CERTAIN COLOR ONLY? | Excel Worksheet Functions | |||
HOW TO FORMATE CELLS TO COUNT CELLS WITH A FILL COLOR? | New Users to Excel | |||
Count cells with the red color | Excel Worksheet Functions | |||
Count cells with the red color | Excel Worksheet Functions |