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

Coloured Cell Count

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

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

Many Thanks

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 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 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 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
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
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 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
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

Similar Topics


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.


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 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 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 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 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.

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

Afternoon all,

I have a spreadsheet with data populating columns A thru BK.

The data is sorted by column A, a five digit number. There might be only one row with a particular five digit number, there might be six or seven.

What I would like to be able to do is alternately colour each row when the value in column A changes,

So at the start of the data, column A in row 2 has a five digit number. This row might be coloured light grey. I need something to test if column A in row 3 contains the same value as row 2 - if it does, the row needs to be coloured light grey as well, if not it needs to be coloured light yellow.

And so on until the end of the data.

I only need columns A thru BK coloured.

I'm sure it's easy for the geniuses (shouldn't that be "geni-i"?) on here but I'm struggling.

Thanks as always.

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

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

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??

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 ?


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


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

I have a spreadsheet that has coloured cells in column a (red, orange or green) and I'd like to write the word corresponding to the cell colour in column b. The cells aren't conditionally formatted, just coloured in.
Any ideas, please?
I'm using xl 2002.


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?


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


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.

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.


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


As an example:
A1 cell colour = Red
A2 cell colour = Blue
A3 cell colour = Yellow
A4 cell colour = Green

I need
B1 cell value = 1
B2 cell value = 2
B3 cell value = 3
B4 cell value = 4

I need a macro code which will make automatically this event.

Cells are example, The code must put the number related to colour to right cell of where is every coloured cell.
(The coloured cells is NOT next each other , left or right. But up or down)

Thanks in advance for any reply and help.


I have a huge spreadsheet of data. Certain cells are coloured yellow. What
I would like is a formula that I can enter in all the cells of the column
that would say: "If this particular cell is coloured yellow then it should
equal O43, otherwise it's to be left blank."

Thank you