Count Cells Based on Font Colour

Using a small routine in Excel VBA you can actually count the number of cells that contain a specific font colour, for example count the number of cells that contain black font.

First step is to create a routine and put it within a module. Once you have done this the routine can be called from the worksheet. So simply copy the code below and paste it into a module:

Sub FontColourCount()

For A = 1 To 10 Step 1

If Cells(A, 1) = "" Then GoTo Here
If Cells(A, 1).Font.ColorIndex = 1 Then
Colour = Colour + 1
End If
Next A

MsgBox Colour

End Sub


Let’s break down the routine that counts the number of cells that contain black font.

For A = 1 To 10 Step 1 – We have declared A as a variable because want to go from row one to row ten. So A will become a number between one and ten as the routine progresses. It will increase in value by one every time a loop is performed.

If Cells(A, 1) = "" Then GoTo Here – When checking the cells for text colour a blank cell will result in a colour index of 1. So if you were checking for black font, as in this example, you will get erroneous results. So this line of code simply states that if the cell is blank then go to Here: where it then progresses the next cycle in the For loop.

If Cells(A, 1).Font.ColorIndex = 1 Then – This line is where we check for font colour. Black has a colour index of 1. Remember that the A in the brackets does not represent column A, the 1 does. A represents the row number that will increase each time a loop is performed.

Colour = Colour + 1 – This line adds 1 to the variable Colour if the font is black. If the font is not black then the routine skips this line and progresses to the End If line.

Here: – This is where the routine comes to if the there is a blank cell. Otherwise it is simply ignored.

Next A – This tells the routine to go back to the beginning and increase the A variable by one.

count-cells-based-on-cell-colour-total-black-fontMsgBox Colour – Once the For loop is complete the routine will display a message box that will give you the total number of cells that contain black font. In my example there were five cells containing black font.

End Sub – This ends the routine.

You can also create a formula that will work based on a cells background colour.

I hope you found this post useful.

2,424 thoughts on “Count Cells Based on Font Colour

  1. Ativar Smart Iptv Lg

    Ponto IPTV a melhor programacao de canais IPTV do Brasil, filmes, series, futebol, lutas, shows, documentarios em alta definicao de imagem e som em SD, HD, FULL HD, 4K. compativel com todos aparelhos do mercado Brasileiro.

  2. решебник по математике для шестого класса el al латотин be d чеботаревский

    Have you ever thought about including a little bit more than just your articles? I mean, what you say is valuable and everything. Nevertheless think of if you added some great photos or videos to give your posts more, “pop”! Your content is excellent but with images and video clips, this site could certainly be one of the most beneficial in its niche. Fantastic blog!| а

  3. udfauzi

    You’re so cool! I don’t believe I’ve truly read something like this before. So good to discover somebody with genuine thoughts on this subject matter. Seriously.. thank you for starting this up. This site is one thing that is needed on the internet, someone with some originality!

  4. Monserrate

    I was pretty pleased to find this great site.

    I want to to thank you for your time for this wonderful read!!

    I definitely enjoyed every bit of it and i also have you book-marked to
    look at new things in your site.

  5. web

    What i don’t understood is in truth how you’re now not actually much more smartly-liked
    than you may be now. You are very intelligent. You know therefore significantly relating to this topic, produced me for my part consider it from numerous varied
    angles. Its like women and men are not interested unless it
    is one thing to accomplish with Lady gaga! Your own stuffs outstanding.
    Always maintain it up!


    Hey I кnoᴡ thiѕ is off topic bᥙt I ᴡаѕ wondering if y᧐u ҝnew of ɑny
    widgets I couⅼd add to my blog that automatically tweet
    my neweѕt twitter updates. I’vе bееn lߋoking for a plug-in lіke
    thiѕ f᧐r quіte some time and wаѕ hoping mаybe yoᥙ
    woսld haᴠe some experience ᴡith something likе this. Please ⅼet mе know if you run into anythіng.
    I truly enjoy reading your blog and I ⅼook forward to your
    new updates.

  7. vivo slot

    It’s nearly impossible to find well-informed people on this subject, but you seem like you know what you’re talking about! Thanks

  8. download aplikasi vivo slot

    Oh my goodness! Incredible article dude! Thank you so much, However I am going through troubles with your RSS. I don’t understand the reason why I am unable to join it. Is there anybody else getting similar RSS issues? Anyone that knows the answer can you kindly respond? Thanks!!

  9. vivoslot

    May I simply say what a comfort to find someone who genuinely knows what they are talking about on the internet. You certainly understand how to bring an issue to light and make it important. More and more people ought to read this and understand this side of the story. I was surprised that you are not more popular because you most certainly have the gift.

Comments are closed.