INTELLIGENT WORK FORUMS FOR COMPUTER PROFESSIONALS
Log In
Come Join Us!
Are you a Computer / IT professional? Join Tek-Tips Forums!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden. Students Click Here
|
VBA Visual Basic for Applications (Microsoft) FAQ
Excel How To
Count a set of cells based on their colour by xlbo
Posted: 2 Sep 05 (Edited 2 Sep 05)
|
It is not actually possible to do this in Excel without VBA. There is no native functionality whatsoever to analyse anything by colour
The following is a VBA function that can be used on a spreadsheet to count coloured cells by their FONT colour or by their BACKGROUND colour.
The following should be copied and pasted into a standard module within the workbook
For those not used to VBA, press Alt + F11 to open the Visual Basic Editor and then use the Insert menu to Insert>New Module
CODEFunction CountColour(Rng As Range, ColourMatch As Integer, BackgroundOrFont As String) 'Rng is the set of cells to be checked 'ColurMatch is the Color INDEX of the colour being tested for 'BackgroundOrFont requires an "F" or "B" to indicate whether to test Font or Background colour
Dim c As Range, TempStore As Long
TempStore = 0
Select Case BackgroundOrFont Case "B" For Each c In Rng If c.Interior.ColorIndex = ColourMatch Then TempStore = TempStore + 1 End If Next Case "F" For Each c In Rng If c.Font.ColorIndex = ColourMatch Then TempStore = TempStore + 1 End If Next Case Else CountColour = "Choose F or B only" Exit Function End Select
CountColour = TempStore
End Function You can use this function on a spreadsheet by entering
=CountColour(A1:A100,3,"B") This will count the number of cells in A1:A100 which have a Background colour of Red (3)
=CountColour(B1:B500,5,"F") This will count the number of cells in B1:B500 that have a Font colour of Blue (5)
The caveat to this is that it will not work if the colouring of the cell is as a result of Conditional Formatting - this requires a lot of extra coding and for the sake of ease, I have not included that here
A few common ColorIndexes
1 Black 2 White 3 Red 4 Green 5 Blue (standard) 6 Yellow 7 Pink 8 Turquoise 15 Light Grey
To obtain a list of all color Indexes, create a new worksheet and run the following code:
CODESub List_Color_Indexes() For i = 1 To 56 Activesheet.Cells(i, 1).Interior.ColorIndex = i Next i End Sub The ColorIndex for the colour shown will be the ROW number |
Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum |
|
|
|
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close