count shaded cells with a gradient fill



Dear Experts:
below macro counts the number of shaded cells in the selection.

I wonder whether the below macro can be re-written to consider
gradient fills as well, i.e. ...
.... e.g. I got a gradient fill with two colors (light blue RGB 220,
230, 242 and dark blue RGB 79, 129, 189), the gradient pattern is

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Sub grey_count()
Dim lastcell As Variant
Dim firstcell As Variant
Dim Cell As Range
Dim x As Long
lastcell = Cells.SpecialCells(xlCellTypeLastCell).Address
firstcell = "A1"
Range(firstcell & ":" & lastcell).Select
For Each Cell In Selection
If Cell.Interior.Color = RGB(234, 234, 234) Then x = x + 1
MsgBox x & " cells are grey (234, 234, 234)"
End Sub

Jim Cone

The easiest way is to check if the cell has shading of any type...
"If ActiveCell.Interior.ColorIndex <> xlColorIndexNone Then x = x + 1"
This should work in all xl versions, however, in xl2007+ all color values were changed so you
should thoroughly test it.
Jim Cone
Portland, Oregon USA
('Shade Data Rows' the way you want them)

"AndreasHermle" <[email protected]>
wrote in message


Actually that should have been "Cell" not "ActiveCell"...
   "If Cell.Interior.ColorIndex <> xlColorIndexNone Then x = x + 1"

"Jim Cone" <[email protected]>
wrote in message

Hi Jim,

oops, I inadvertently did not give you any feedback to your post.
Sorry about this.

Ok, thank you very much for your valuable and professional help. This
is a very good piece of advice.

Regards, Andreas

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question