count shaded cells with a gradient fill

  • Thread starter Thread starter AndreasHermle
  • Start date Start date
A

AndreasHermle

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

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
Next
MsgBox x & " cells are grey (234, 234, 234)"
End Sub
 
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
http://www.mediafire.com/PrimitiveSoftware
('Shade Data Rows' the way you want them)



"AndreasHermle" <[email protected]>
wrote in message
news:ecc4a38c-c281-4bf6-836c-fcec9cc5044c@en1g2000vbb.googlegroups.com...
 
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
 
Back
Top