Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Count Coloured Cells

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


Please see the attached file

Can anybody tell me how to count coloured cells in excel,

For example, I have numbers with red , I want to count

how many Red coloured cells are in each line.


View Answers     

Similar Excel Tutorials

Count The Number of Words in a Cell or Range of Cells in Excel - UDF
Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that ...
Count the Number of Cells that Start or End with Specific Text in Excel
How to count cells that match text at the start or the end of a string in Excel. If you want a fuller explanation o ...
Count the Number of Cells that Contain Specific Text in Excel
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell ...

Helpful Excel Macros

Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
- UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell the f
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se

Similar Topics

Hi. Can anyone tell me how in excel 2003 you can count the number of coloured cells in a column.

Many Thanks


I used Conditional Formatting to highlight cells containing specific values (i.e. less than, between, etc...). I now need to show a count of how many cells are green, red, yellow, etc...

I have been reading about having to create a User Defined Function using VBA, but after various tests, I can only achieve the desired results if I manually highlighted the cells (i.e. highlighted the cell and filled it with colour).

How can I count the cells which were highlighted using Conditional Formatting?

I am using Excel 2007.


Hi There,

I was wondering if anyone know a formaula for counting the coloured cells in a conditional formating senario? I have a RAG analysis and would liek to count teh number of amber and red's?




I have had a look through some searches and I can't find any that have answered this question before so would appreciate it if someone could assist.

I have a few cells on a sheet which will be coloured red. They won't always be the same cell references, but they will always be the same colour.

I was wondering if there was some VBA code somewhere which would allow me to put in cell D1 the total amount of the coloured cells.

Please, if you need more info, just ask'

Kind Regards

Hi All

I have a feeling that this is a dumb beginners question.

I have a worksheet containing a large column with random coloured (red) cells, how can I Sum only the coloured cells and then only the empty cells?

TIA thalt

All, please excuse me if I have posted in the wrong forum. Am at my wits end.....

I want to do a mail merge into MS Word. The speadsheet I am using has many coloured cells (green) to indicate changes between earlier and upated info.

My questions -

(1) is there any way to perform a mail merge on the info contained only in the coloured cells?


(2) is there any way that merged info in the document will be formatted differently (italics, or coloured, or...) to indicate that the data has come from a coloured cell in Excel?

Many thanks, c.

Hi all

I have a sheet with colour coded cells and I need a macro that counts the number of rows with coloured cells.

I've tried the following code but I always get a count of 0 which isn't right.


Sub Count_Coloured_Cells()
    Dim i, c
    Dim LastRow As Long
    i = 1
    c = 0
    LastRow = Range("A65536").End(xlUp).Row
    Do While i < LastRow + 1
        If Rows(1 + i & ":" & 1 + i).Interior.ColorIndex  xlNone Then
        c = c + 1
        End If
    i = i + 1

    MsgBox (c)

End Sub

Remember, it's the number of rows I need to count (i.e. one row may have several colour coded cells but I only need to count it once).

Thanks in advance

I am trying to find a way to be able to count only the coloured cells in a column. Is this possible?

Hi Please can you tell me is it possible to mail merge coloured cells

If yes > what is the switch?
If no > How do I copy across coloured cells to other excel spreadsheets using vba?

many thnaks


I am looking to use a coloured wingding character to indicate when a cell reaches a certain percentages, as follows:

If Range("B3").value = > 65% then add wingding 2 coloured Green
If Range("B3").value = > 75 % then add wingding 2 coloured Orange
If Range ("B3").value > 85% then add wingding 2 coloured Red

Any guidance is much appreciated

Hi all,

Using MS Excel 2003, I want to create a chart that has coloured horizontal bands as a background with the data lines on top of these bands.

E.g. If all of my y-axis values will be in the range 0-30, I'd like to split the background into 3 horizontal stripes such that:

0 <=10 is coloured gold
10<=20 is coloured silver
20<=30 is coloured bronze

On top of these stripes are my actual data lines.

It seems like it should be possible, but I'm just too dense to figure it out. Any ideas please?


Hi - is there any way to sum up numbers contained in cells that are coloured yellow only?

Hi there,

I need to count the number of cells that are shaded a particular colour.

For example, if five cells in a worksheet are coloured pink, then the value should show 5.

Is this possible? Please help.

Thank you

Can I use a countif formula to count cells which have certain colour fills but no value in them (or a value not relevant)?
If so, what would the criteria be?


Dear sir, good evening,is it possible in excel 2003 to filter coloured cells or rows as mentioned in attached file and count or sum the filtered cells.
Thanking you in advance.

does any one know how to do this?

A macro which will auto track a row of B1 to F1 and B2 to F2.
If there is any coloured cells(lets say orange),

Macro auto merge cells from the start coloured cell to the last coloured cell and input a value in A1 and A2??

How can I count cells which are formatted in colour? I have tried the
countif function, but it does not work. I cannot use the conditional
formatting, because the cell values contain only text, which is not
repeating. So I have to manually colour the cells.

Please advise.


I have a similar problem - to a thread with the same title

I'm attempting to count cells that have been conditionally format based on the criteria that it matched the value in another cell. i.e. drawn lotto numbers that match my numbers played for particular draw.

I'm looking to count the number of cells that are formatted.
Conditional format applied to my number that match drawn number which have been input on sheet.

=COUNTIF($B3:$AX3,BB3)>0 :: cells are coloured blue applies to :: =$BB$3:$EK$2918


I have sheet will different colour in different cells. Say if i want to count how many yellow coloured cells are there what is the formula ?


See Attached Doc.

Can I use a CountIf formula to count coloured cells that have been formatted? I tried to use a CountIf formula to count the % but because some of the warning and failing % are different that would distort my results.

Thanks very much!!!

Looking at this function ; i can only see how it allows you to represent 'visually' a value in a cell in terms of a coloured bar. with the colour changing depending on the magnitude of the value.

I would like to use the function to represent two lots of data (from two other cells )shown with two coloured bars within one cell, so that each bar is end to end within one cell, then with 'spare' length at the end of the bar representing 'un-used' capacity.

Can anyone advise ?

I have a table that consists of a list of activities in column A and a matrix of values in columns B to H. I have coloured some of the cells within the matrix blue (ColorIndex 47).

I need a macro that will look across the matrix and everytime it comes across a cell (or couple of cells) in a row that is coloured blue, will build a list of the corresponding activities (from column A) and put the sum total of the corresponding coloured cells next to the listed activities.

Much appreciated

Hi All,

I am trying to figure out how to create an array of cell addresses based on the interior color index of a cell.

The spreadsheet I have created deals with attribute data. The end user will update the attribute data in the spreadsheet. When a change occurs the cell become highlighted and bold.

Here is my Code So Far which identifies the row that you are working in and then selects them all and does a count of the highlighted cells.


Sub SumIf_Highlighted()

Application.ScreenUpdating = False

Dim FCol
Dim LCol
Dim NRow
Dim rng As Range

Set rng = ActiveCell

FCol = "H"
LCol = "BZ"
NRow = ActiveCell.row

Range((FCol & NRow) & ":" & (LCol & NRow)).Select

x = Selection.Cells.Count

For Each c In Selection
    If Selection.Interior.ColorIndex = 44 Then
        Count = Count + 1
    End If

Application.ScreenUpdating = True

MsgBox Count & " coloured cells"
MsgBox x - Count & " Non coloured Cells"

End Sub

What needs to be added it the section on creating the array with all the saved cell addresses.

Once this is completed the next step with be to use this array and populate Notes field (another cell) with information on the changes those fields. Any assistance would be very grateful as I am unsure of what to do.

Many thanks


Hi guys,

I have a spreadsheet and a module that allows me to count coloured cells.

If the cell with a colour in has ANY text in it, then I need it to count it as 0.5 instead of 1.

Any ideas?

Current Code:


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


'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


        For Each rCell In rRange

            If rCell.Interior.ColorIndex = lCol Then

                vResult = 1 + vResult

            End If

        Next rCell

    End If

   ColorFunction = vResult

End Function



I have a sheet that imports some data. Sometimes there is one or more than one cells in the range D5:CY5 that are coloured black, whilst sometimes there are no cells coloured black.

I was wondering is there a way to have the sheet look to see if there are any black cells in that range, and if so write some text into cell E18.

thanks in advance