Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Highlighted numbers in an area
I have a tough and unusual question that I'm not sure can be done.
I have a spreadsheet that has different cells highlighted in two different colors. I am trying to add up the number of times a highlighted number (of the two colors) appear in a section. I am trying to search A1..AS2 , A4..E26, F4..O8. The two colors to look for a Color index= 39 or 4 Pattern= xlsolid I am looking for a total of highlighted numbers that include both colors. The numbers I would be searching for are from 1-9 and I would like to find out how many 1's, 2's, 3's...etc. Example: #1= 4 times #2= 6 times #3= 0 times #4= 2 times |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Highlighted numbers in an area
Hopefully this macro will do the job for you. Just have the sheet with those
ranges you need to check selected when you use Tools | Macro | Macros to run it. To put it into your workbook, open the workbook and press [Alt]+[F11] to enter the VB Editor. Choose Insert | Module and then copy the code below and paste it into the empty module presented to you. Sub CountByColors() Dim valueCounts(1 To 9) As Integer Dim seekRange As Range Dim anySeekEntry As Range Dim resultsMessage As String Dim LC As Integer Const ci39 = 39 Const ci4 = 4 'assumes sheet in question is active sheet Set seekRange = Range("A1:AS2") For Each anySeekEntry In seekRange If anySeekEntry.Interior.ColorIndex = ci4 Or _ anySeekEntry.Interior.ColorIndex = ci39 Then If anySeekEntry = 1 And anySeekEntry <= 9 Then valueCounts(anySeekEntry.Value) = valueCounts(anySeekEntry.Value) + 1 End If End If Next Set seekRange = Range("A4:E26") For Each anySeekEntry In seekRange If anySeekEntry.Interior.ColorIndex = ci4 Or _ anySeekEntry.Interior.ColorIndex = ci39 Then If anySeekEntry = 1 And anySeekEntry <= 9 Then valueCounts(anySeekEntry.Value) = valueCounts(anySeekEntry.Value) + 1 End If End If Next Set seekRange = Range("F4:O8") For Each anySeekEntry In seekRange If anySeekEntry.Interior.ColorIndex = ci4 Or _ anySeekEntry.Interior.ColorIndex = ci39 Then If anySeekEntry = 1 And anySeekEntry <= 9 Then valueCounts(anySeekEntry.Value) = valueCounts(anySeekEntry.Value) + 1 End If End If Next resultsMessage = "Results: " & vbCrLf For LC = LBound(valueCounts) To UBound(valueCounts) resultsMessage = resultsMessage & "#" & LC & " = " & _ valueCounts(LC) & vbCrLf Next MsgBox resultsMessage, vbOKOnly, "Your Results" Set seekRange = Nothing ' housecleaning End Sub "basic" wrote: I have a tough and unusual question that I'm not sure can be done. I have a spreadsheet that has different cells highlighted in two different colors. I am trying to add up the number of times a highlighted number (of the two colors) appear in a section. I am trying to search A1..AS2 , A4..E26, F4..O8. The two colors to look for a Color index= 39 or 4 Pattern= xlsolid I am looking for a total of highlighted numbers that include both colors. The numbers I would be searching for are from 1-9 and I would like to find out how many 1's, 2's, 3's...etc. Example: #1= 4 times #2= 6 times #3= 0 times #4= 2 times |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Highlighted numbers in an area
Thanks J,
Very Impressive! This is exactly what I what I am looking 4. The only problem is I told you I was looking for highlighted font. I should have said highlighted fill. The background is highlighted in these colors not the actual font. Is there anyway to have it look for the fill not the font. Thanks again, Tom "JLatham" wrote: Hopefully this macro will do the job for you. Just have the sheet with those ranges you need to check selected when you use Tools | Macro | Macros to run it. To put it into your workbook, open the workbook and press [Alt]+[F11] to enter the VB Editor. Choose Insert | Module and then copy the code below and paste it into the empty module presented to you. Sub CountByColors() Dim valueCounts(1 To 9) As Integer Dim seekRange As Range Dim anySeekEntry As Range Dim resultsMessage As String Dim LC As Integer Const ci39 = 39 Const ci4 = 4 'assumes sheet in question is active sheet Set seekRange = Range("A1:AS2") For Each anySeekEntry In seekRange If anySeekEntry.Interior.ColorIndex = ci4 Or _ anySeekEntry.Interior.ColorIndex = ci39 Then If anySeekEntry = 1 And anySeekEntry <= 9 Then valueCounts(anySeekEntry.Value) = valueCounts(anySeekEntry.Value) + 1 End If End If Next Set seekRange = Range("A4:E26") For Each anySeekEntry In seekRange If anySeekEntry.Interior.ColorIndex = ci4 Or _ anySeekEntry.Interior.ColorIndex = ci39 Then If anySeekEntry = 1 And anySeekEntry <= 9 Then valueCounts(anySeekEntry.Value) = valueCounts(anySeekEntry.Value) + 1 End If End If Next Set seekRange = Range("F4:O8") For Each anySeekEntry In seekRange If anySeekEntry.Interior.ColorIndex = ci4 Or _ anySeekEntry.Interior.ColorIndex = ci39 Then If anySeekEntry = 1 And anySeekEntry <= 9 Then valueCounts(anySeekEntry.Value) = valueCounts(anySeekEntry.Value) + 1 End If End If Next resultsMessage = "Results: " & vbCrLf For LC = LBound(valueCounts) To UBound(valueCounts) resultsMessage = resultsMessage & "#" & LC & " = " & _ valueCounts(LC) & vbCrLf Next MsgBox resultsMessage, vbOKOnly, "Your Results" Set seekRange = Nothing ' housecleaning End Sub "basic" wrote: I have a tough and unusual question that I'm not sure can be done. I have a spreadsheet that has different cells highlighted in two different colors. I am trying to add up the number of times a highlighted number (of the two colors) appear in a section. I am trying to search A1..AS2 , A4..E26, F4..O8. The two colors to look for a Color index= 39 or 4 Pattern= xlsolid I am looking for a total of highlighted numbers that include both colors. The numbers I would be searching for are from 1-9 and I would like to find out how many 1's, 2's, 3's...etc. Example: #1= 4 times #2= 6 times #3= 0 times #4= 2 times |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding area codes only to phone numbers missing an area code | Excel Discussion (Misc queries) | |||
COUNT HIGHLIGHTED CELLS IN A ROW ? | Excel Discussion (Misc queries) | |||
Highlighted Columns Count! | Excel Discussion (Misc queries) | |||
Count Highlighted Rows | Excel Discussion (Misc queries) | |||
Count highlighted cells | Excel Worksheet Functions |