Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Subtotal By Cell Color

Count Sum By Color. I have an enormous sheet of 6000+ records. I have a macro running that permits automatic counting of cells according to their background colour. What I would like to be able to do when selecting some records is for the colour count to recognize this, and not continue to count the hidden data. Subtotal works fine regarding the displayed records, but can it be used in conjunction with the colorfunction macro so as to only count the selected record colours where applicable. I would also like to be able to sort according to the background colour, but without adding another column. Are there any boffins out there who can help???????? Please, coz it's driving me nuts!!


	VB:
	
 
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) 
    Dim rCell As Range 
    Dim lCol As Long 
    Dim vResult 
     
     
    lCol = rColor.Interior.ColorIndex 
     
    If SUM = True Then 
        For Each rCell In rRange 
            If rCell.Interior.ColorIndex = lCol Then 
                vResult = WorksheetFunction.SUM(rCell, vResult) 
            End If 
        Next rCell 
    Else 
        For Each rCell In rRange 
            If rCell.Interior.ColorIndex = lCol Then 
                vResult = 1 + vResult 
            End If 
        Next rCell 
    End If 
     
    ColorFunction = vResult 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Cell formula =colorfunction($H$10,$A:$A,FALSE) - counts colours
=subtotal(3,B12:B6000)


Post your answer or comment

comments powered by Disqus
How can I sum by cell color in Excel 2003?

Hello,

I am new to excel and new to this forum. I am currently trying to move data from a Master worksheet, called "Master". In the "Master Worksheet, all the data will be sorted by cell colors (Yellow, Green, Blue, Red and White(or no color)). I need this data to be sorted into separate a separate worksheet for each of the colors. Is there a way to do this in excel with formulas? or do I need to use VBA? And how would I do this?

Thanks!

I have a worksheet with information separated by cell color. I want to pull the data from cells of a certain color, without manually going down the list and "copy-pasting". I know I can sort by cell color, but I want something that will automatically populate with data of a certain color. I don't want to change the order of the raw data by keeping all the same colors together.

What I'm not trying to do: Change the cell color of data that meets my criteria. I know this is easily done with Conditional formatting.
What I am trying to do: Get a list of data that is already a certain cell color.

I've uploaded a simplified file to help explain. In it, there are 3 sets of data (Site 1, Site 2, Site 3). I want to create a list of the data in green cells, which happens to be all of Site 1 and Site 3.

I don't know if this can be accomplished with any built in functions, or if it will require some type of script or macro?

Thanks!

I have a sheet 6 cells wide and 44 cells long. Each cell contains a movie title. The background color of the cell notates the genre of the movie (i.e. comedy, drama, etc.) I need to filter out the entire page by individual cell color. So that, for example, I can print a list of only comedies (only blue cells), but also print a master list of all of the movies. Whenever I choose filter, it filters rows by individual cell. I need to filter each cell independently. I also need to filter out separate lists based on italics, bold, and normal font as this dictates ownership of the movies. Are either of these things accomplishable? Thanks!!!

Hi,

Can anyone let me know if I can sort a column by the color of the cell?

tks,
steve

I have a list of names in a spreadsheet and have color-coded certain ones.
Is there a way to sort by the color of the cell or row?

Thanks,
Ted

I found Dave's thread on how to count cells by fill color and I am using ColorFunction.
http://www.ozgrid.com/forum/showthread.php?t=27182

I have also found how to count the unique values in a column.

Now what I need to combine them to look at the yellow cells (fill, not font) and tell me how many unique values there are. There are no blanks in the column.

For example. I need th following to return 3, not 5.

tshqas193a
tshqas194b
dvhqas055
dvhqas143
dvhqas323
dvhqas046
dvociw001
dvociw001
dvociw001

Hi,
ColorFunction is a great tool, but I would like to use it to calculate the Standard Deviation of color filled cells. Can the following code be modified to do this?
Thanks for your help.


	VB:
	
 
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) 
    Dim rCell As Range 
    Dim lCol As Long 
    Dim vResult 
     
     ''''''''''''''''''''''''''''''''''''''
     'Written by Ozgrid Business Applications
     'www.ozgrid.com
     
     'Sums or counts cells based on a specified fill color.
     '''''''''''''''''''''''''''''''''''''''
     
     
     
    lCol = rColor.Interior.ColorIndex 
     
    If SUM = True Then 
        For Each rCell In rRange 
            If rCell.Interior.ColorIndex = lCol Then 
                vResult = WorksheetFunction.SUM(rCell,vResult) 
            End If 
        Next rCell 
    Else 
        For Each rCell In rRange 
            If rCell.Interior.ColorIndex = lCol Then 
                vResult = 1 + vResult 
            End If 
        Next rCell 
    End If 
     
    ColorFunction = vResult 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I was wondering if you all know of a code that will allow me to count cells based on the color of the text.

For example:

Column A
Apple
Boy
Cat
Dog

I would like to return a count of 3 in another cell.

(I did find the count cell by background color code...which is great!, but I couldn't find one for text).

Thanks

How can I sort by cell backgroung color?

I was working on a formula in my budget.

Right now, all deductions and deposits are entered in the same column
(column B). With the current balance in the account in column c.

I differentiate between what I spend the money on by highlighting the cell
in column b a specific color or shade.

What I want to do in column E is keep track of money spent per color. Just
one color specifically (gray-25% - index 15).

Is it possible to have a conditional format in an IF formula?

I was thinking something like:

IF(B721=(interior.colorindex=15),SUM(E720+B721),E7 20)

I tried it but no cigar... am I close at all?

Is it even possible?

Thanks

James
--
J B
IT Professional
DC/VA

I have a vacation schedule for employees. As they turn in a vacation request,
I add it to the spreadsheet as pending. Once it is approved by management, I
would like to click the cell and have it turn green and the letter 'A' appear
as approved. If it is denied, the cell should be turned red and the letter
'D' inserted. The original input cell has a '1', '2', '3' (ist, 2nd, 3rd
chocie) or a V as a non-choice vacation (usually a one day request. The
pending status is always normal white cell shading. Is there any way of
cycling through the cell color choices....green to red...as I click on the
cell ? I'm sure I would have to do each cell separatly (2 week vacation, for
example) but that would still be easier than highlighting and re-typing every
cell. I did notice a 2007 posting for changing from one color to the next,
but not sure about cycling through with multiple choices.

Hi All!

New guy to the forum. I'm a business/application consultant for ERP software, and generally pretty solid when it comes to excel. However, I've come across a question for a personal sheet that I can't seem to solve.

In my personal budget worksheet, I'd like to set a budget on a cell-by-cell basis. When the budget is hit, I'd like the text color (or cell color) to change.

I.E. Cell D14 has a budget of $200. When I enter $200 in the cell, the text turns from black to red.

I've searched through google with no luck. I have mildly searched this forum, but haven't found my direct answer - I have come pretty close though, in other languages

Any help?

TIA!

Have a worksheet with different cell colors.

Is there a way to sort these by color?

Thanks!

Hello,

I would like to be able to count cells by color and by cell content. I know a custom function that can count cells that are highlighted in a certain color but I only want to count the cells that also have an "A". So in my range there are cells of different colors and different values but I only want to count the ones that are say green and have "A"

Thanks.

My worksheet event change code allows me to set cell colors based on certain cell values, however, the value in the cells are pasted links from another sheet in workbook and does not change the cell color automatically it only works after pressing F2 then enter to re-calculate cell.
Something must be wrong in code as what I'm trying to achieve isn't possible with what code has been used, to aid my explanations further the code is shown below


	VB:
	
 Range) 
    Dim Icolor As Integer 
    If Not Intersect(Target, Sheets("Personnel COPY").Range("c27:Ah259")) Is Nothing Then 
        Select Case Target 
        Case 2 
            Icolor = 6 
        Case 3 To 4 
            Icolor = 8 
        Case 5 
            Icolor = 46 
        Case 7 
            Icolor = 6 
        Case 8 
            Icolor = 7 
        Case 9 
            Icolor = 4 
        Case "A" 
            Icolor = 4 
        Case "B" 
            Icolor = 39 
        Case "E" 
            Icolor = 39 
        Case "H" 
            Icolor = 12 
        Case "J" 
            Icolor = 7 
        Case "L" 
            Icolor = 4 
        Case "M" 
            Icolor = 4 
        Case "N" 
            Icolor = 3 
        Case "P" 
            Icolor = 46 
        Case "R" 
            Icolor = 4 
        Case "O" 
            Icolor = 4 
        Case Else 
             'Whatever
        End Select 
         
        Target.Interior.ColorIndex = Icolor 
         
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Also tried adding the following but doesn't change anything

	VB:
	
 
Application.ScreenUpdating = True 
Range("c27:Ah259").Calculate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Additonal Edit
For example sheet 1 has either text or numbers in range C:C and all i've done is copied the range and used paste special as link to sheet 2

I've inherited a spreadsheet that appears to use a macro to change the color of a cell based on the selection made from the validation list applied to the cell. Example: The cells' data validation criterian is a list consisting of the following strings; In-Process, Completed, On-Hold. The validation also allows blank entries. When the cell is blank, no color is applied. When I select "on-hold" the cell turns pinkish, "In-Process" turns the cell yellow and "Completed" fills it green. I looked everywhere to see how this was being done and all I could find was a macro...so I am assuming this somehow is making it happen (I could be sooo wrong though). Unfortunately, I can't figure the macro out to either prove/disprove my assumption.
Could anyone tell me if the following macro is responsible for the cell color changes? And if it is, possibly shed some light on what the formular1c1 setting means?

	VB:
	
 Macro9() 
     '
     ' Macro9 Macro
     ' Macro recorded 1/26/2006 by temp
     '
     ' Keyboard Shortcut: Ctrl+q
     '
    ActiveCell.FormulaR1C1 = "=+RC[1]/R[-2]C[2]" 
    Range("G5").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks,
Pat

Hi all,
I found a really excellent function on the net that you guys might have seen before but that I wanted to share. This formula sums cells according to colour. Neat huh?

Robert


	VB:
	
 
     
    Dim TempRange As Range 
    Dim Result 
    Dim Colour 
     
    Application.Volatile 
    On Error Goto BailOut 
    Colour = TheColourCell.Interior.Color 
     
    For Each TempRange In TheRange 
        If Colour = TempRange.Interior.Color Then Result = Result + TempRange.Value 
    Next 
     
BailOut: 
    SUMIFCOLOUR = Result 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I would like for a vba programmer to tell me if the following program can be written before I attempt to code it. When I open Excel I want to see cells containing names in a square array with red backgrounds. As I click on each cell I want the cell to turn green and I want an alphabetized column of the names to have a 1 recorded(after I click) indicating that the person is present. If a person's name is red then I need a 0 recorded. If possible, I would like to be able move over one column each day that I take attendance by clicking on a cell in a row above the column of names. I am a math professor and I write computer code most every day but I have only written some small routines in vba that did not involve events.

At work I am getting java errors whenever I try to use 'search' and it won't display results, but I have searched all over google with no luck. I'm trying to count how many cells are filled with a specific color and only the ones that have data in them. The following formula returns 0

=COUNTIF(B47:O146,AND(COUNTIF(B47:O146,">"""),ColorFunction(O14,B47:O146)))

Seperatley, the counts work, I just can't figure out how to combine them.

Im running into trouble with some conditional formatting. Im attaching an example I used for another question I had earlier to help with this issue so some parts are not completely relevant in the excel sheet. What you need to now is if you push the record button it populates a list down always below the last written one in column B. Column A are colors. The colors are based on the numbers, if you change a number the color changes as written out in this code


	VB:
	
 Range) 
    Dim rgArea As Range, rgCell As Range 
    Dim iColor As Integer 
     
     '   Get the intersect of the target & the proper range
    Set Target = Intersect(Target, Range("A26:A40")) 
     
     '   If this intersection exists
    If (Not Target Is Nothing) Then 
         '   For each subsection of the selection
        For Each rgArea In Target.Areas 
             '   For each cell of the subsection
            For Each rgCell In rgArea.Cells 
                With rgCell 
                     '   Choose colour index based on value
                    Select Case .Value 
                    Case 1 
                        iColor = 3 
                    Case 2 
                        iColor = 4 
                    Case 3 
                        iColor = 6 
                    Case 4 
                        iColor = 7 
                    Case 5 
                        iColor = 8 
                    Case 6 
                        iColor = 9 
                    Case 7 
                        iColor = 10 
                    Case 8 
                        iColor = 11 
                    Case 9 
                        iColor = 14 
                    Case 10 
                        iColor = 17 
                    Case 11 
                        iColor = 19 
                    Case 12 
                        iColor = 22 
                    Case 13 
                        iColor = 23 
                    Case 14 
                        iColor = 30 
                    Case 15 
                        iColor = 31 
                    Case 16 
                        iColor = 32 
                    Case 17 
                        iColor = 33 
                    Case 18 
                        iColor = 34 
                    Case 19 
                        iColor = 35 
                    Case 20 
                        iColor = 36 
                    Case 21 
                        iColor = 37 
                    Case 22 
                        iColor = 38 
                    Case 23 
                        iColor = 39 
                    Case 24 
                        iColor = 40 
                    Case 25 
                        iColor = 42 
                    Case 26 
                        iColor = 43 
                    Case 27 
                        iColor = 44 
                    Case 28 
                        iColor = 45 
                    Case 29 
                        iColor = 46 
                    Case 30 
                        iColor = 47 
                    Case 31 
                        iColor = 49 
                    Case 32 
                        iColor = 54 
                    Case Else 
                        iColor = xlNone 
                    End Select 
                     
                     '   Set the cell's background colour
                    .Interior.ColorIndex = iColor 
                End With 
            Next rgCell 
        Next rgArea 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I want to do is make the giant orange rectangle in the middle change to the color thats in column A of the next blank row down in the populating list. I cant simply use the numbers in the orange area like i do in column A because it will have text in it.

So I guess the orange area needs to match the color of 1 down and 1 left of this code which constantly changes.

	VB:
	
 Record() 
     '
     ' Record Macro
     ' Macro recorded 8/4/2006 by denkingerw
     '
     
    Range("A15:G15").Copy 
    Range("B65536").End(xlUp).Select 
    ActiveCell.Offset(1, 0).Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I tried to do this with conditional formating but found even if I get the offset I can only have it change to a certain cell color and each of the orange cells needs to be able to change into one of 32 different colors based on the numbers associated with them.

Any help would be greatly appreaciated.

denkinge

Hello
I've made a little macros to make a sort by Font color. Here i put hide.The problem is that i have a blanks cells in column that i to sort the macros is stopping .I need some help for this issue, maybe i should chose to make an filter instead of hide??? Also the macros has to ignore blanks.
Here is the code
Code:
Sub FILTER_FONTCOLOR()

'On gèle l'écran
Application.ScreenUpdating = False

'Déclaration des variables
Dim i As Long
Dim coll As Integer
Dim color As Integer
coll = InputBox(Prompt:="Choisir Colonne a filtrer")
color = InputBox(Prompt:="Choisir Couleur a filtrer")
'Code
i = 2 'En partant de la 2ème ligne, on peut conserver une ligne d'entête
While Not IsEmpty(Cells(i, coll))
If Not Cells(i, coll).Font.ColorIndex = color Then
Rows(i).EntireRow.Hidden = True
End If

i = i + 1
Wend

End Sub
Thanks

I have the following.
A B C
1 23000 56 123

2 24000 45 234

3 24000 65 126

4 25000 32 87

(Data in row 2 is red text)

If I wished to use lookup can I reference by text color? Thanks in advance. Larry

I am running Excel 2003 SP2 and have been trying to figure this out for 2
days. I have reviewed all of he postings and cannot get this to work. I have
a 4 sheet workbook. In cell A16 of the 3rd worksheet I have the formula
=IF(AND F10>=2,F11>=2,F12>=2),"COMPLETED", "NOT COMPLETED") I also have
conditional formatting applied to this cell so that when "COMPLETED" is
displayed, the cell color is #4 (green) and when "NOT COMPLETED" is
displayed, the cell color is #3 (red). I would like to have the worksheet tab
color mirror this cell color. That way, when I am viewing any other
worksheet, I can simply look at the tab for the 3rd worksheet and determine
by the tab color if it is COMPLETED or NOT COMPLETED. I would really like to
get this working as I have been told it can't be done. Thanks in advance for
any help.


No luck finding an answer? You could always try Google.