counting color format in excell

[scottian]

I want to count the background color of cells within excell, but cant find the color values. can anyone help

 

[JohnAcc]

Hi There,

You need to use the interior property of the cell. i.e

msgbox cells(1,1).Interior.Colorindex

Rgds, John

 

[Chattin]

http://www.cpearson.com/excel/colors.htm

And to list the colours with their index numbers. Run on a blank ws

Sub ColorIndexList()

' Begin error trapping.
On Error GoTo Done

Range("A1&quot.Select
' Put the word Color in active cell.
ActiveCell.formula = "Color"
' Put the words Color Index Number one cell to right of active cell.
ActiveCell.Offset(0, 1).formula = "Color Index Number"

' Select one cell down from active cell.
ActiveCell.Offset(1, 0).Activate

' Begin loop from 1 to 56.
For xColor = 1 To 56

' Apply color and pattern properties to active cell.
With ActiveCell.Interior
.ColorIndex = xColor
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

' Put color index in cell to right of active cell.
ActiveCell.Offset(0, 1).formula = xColor

' Select next cell down.
ActiveCell.Offset(1, 0).Activate

' Increment For loop.
Next xColor

Done:

End Sub

 

[SkipVought]

Hi,

Here's chattin's sub WITHOUT Selecting cells. See faq707-4105 How Can I Make My Code Run Faster?

Sub ColorIndexList()

    ' Begin error trapping.
    On Error GoTo Done

    With [A1]
        .Value = "Color"
       ' Put the words Color Index Number one cell to right
        .Offset(0, 1).Value = "Color Index Number"
    End With
       ' Select one cell down from active cell.
       ' Begin loop from 1 to 56.
    For xColor = 1 To 56

          ' Apply color and pattern properties
        With Cells(xColor + 1, 1)
            With .Interior
                .ColorIndex = xColor
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
           .Offset(0, 1).Value = xColor
        End With

    Next xColor
    Exit Sub
Done:
    MsgBox "ERROR: " & xColor
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com