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

count shaded cells with a gradient fill
 

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"
wrote in message
...
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

count shaded cells with a gradient fill
 

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




"Jim Cone"
wrote in message
...

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)

count shaded cells with a gradient fill
 

On Jul 24, 2:12*am, "Jim Cone" wrote:
Actually that should have been "Cell" not "ActiveCell"...
* *"If Cell.Interior.ColorIndex < xlColorIndexNone Then x = x + 1"

"Jim Cone"
wrote in ...









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)

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