Loop through Excel's Colors and patterns 1

[RonRepp]

Hi all:

How do you loop through Excel's cell colors/patterns? If anyone knows, I'd appreciate where to look.

Thanks,

Ron



Ron Repp

 

[xlbo]

how do you mean ??

Excel has all the colours available to your computer - you can set ANY combination of colours up in excel

If you want to see what colours are available to the current session then:

For i = 1 to 56
cells(i,1).interior.colorindex = i
next i

will output coloured cells in column A of the active worksheet...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[RonRepp]

Geoff:

Thanks, that works well for the colors, but how do I know which color is which integer?

Also, isn't there a collection object for the patterns inside a cell, i.e. Selection.Interior.Pattern = xlGray8.

There are so few patterns that I could just run macros until I got all of them, but I prefer to do it the proper way.

Thanks,

Ron



Ron Repp

 

[xlbo]

Q: but how do I know which color is which integer?

A: try the row number

and no - there is no collection - just a set of constants

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

You can read/set workbook's color palette using Color(ColorIndex) property that keeps RGB value (Long), RGB function can be useful to set colour.

combo

 

[RonRepp]

Hi again:

Geoff, your code worked great to set the colors using color index; thanks, I needed that. However, what I'm trying to do is create a form with labels on it and set the backcolor property to the color index, so that when a user clicks on the label, it will color the cell.

Combo, I tried reading the color by using your suggestion, but keeps erring out on me.

Although this is not exactly what I had in mind, I'm trying to read the color name (i.e., White when colorindex = 2) for a tooltiptext.

This is what I have so far. The second portion is where it errs.

=========================
Sub Colours()
Dim i As Long
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Cells(i, 1).Value = i
Next i
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
MsgBox Cells(i, 1).Interior.Color(ColorIndex)
Next
End Sub
===========================
Thanks,

Ron

Ron Repp

 

[combo]

It is not possible to get colour having only ColorIndex value, as the colour can be redefined to any. You need rgb value, that can be accessed either directly or via palette.
A palette is worbook's setting, it can be useful when you redefine colors, for instance (n - 1-56 colorindex, r,g,b - 0-255 rgb settings. I missed 's' in my first post.):
ThisWorkbook.Colors=RGB(r,g,b)
equivalent to Tools>Options>Colour tab.

You can read (rgb) colour either from workbook's palette:
Msgbox ThisWorkbook.Colors
or directly:
Msgbox Cells(i, 1).Interior.Color

Having RGB (=r + g * 256 + b * 256^2), (r,g,b) can be calculated.
VBA can use eight predefined colour constants (vbBlack, vbBlue, vbCyan,..) that keep rgb value, they can be used to set those colours or do exact match.

combo

 

[RonRepp]

Combo:

This helps a lot. I guess I forgot to mention that I'm colorblind, which is part of the reason for this task.

Thanks again.

Ron

Ron Repp

 

[comaboy]

RonRepp

This might help you. It's a quick'n'dirty macro that will generate all the colours. The cell background is the actuall RGB value, I've "reversed" the foreground colour for legibility. BTW, it'll take AGES to run so I've added a Step 10 on the loops to speed things up. If this is what you're after, just change it Step 1 for every possible combination!

Sub Macro1()
' thrown together by Comaboy
    Dim R As Integer
    Dim G As Integer
    Dim B As Integer
    Dim x
    x = 2
    Dim sht As Worksheet
    For R = 0 To 255
        Set sht = Worksheets.Add
        sht.Name = "Red " & R
        sht.Activate
            For G = 0 To 255 Step 10 'for speed
            For B = 0 To 255 Step 10 'for speed
                 Range("A1").Offset(G, B).Value = R & "," & G & "," & B
                 Range("A1").Offset(G, B).Interior.Color = RGB(R, G, B)
                 Range("A1").Offset(G, B).Font.Color = RGB(255 - R, 255 - G, 255 - B)
            Next
            x = x + 1
        Next
    Next
End Sub

BTW this is not fully tested. If anyone wants to correct/play/whatever with it, feel free !

 

[combo]

Hi comaboy,
actually, you can't set precise rgb colour of cell vith Color property, even if there is no error reported and it is r/w. What excel does is matching to nearest colour in palette, in general, for cell c:
' after:
c.Interior.Color=RGB(r,g,b)
' still holds
(c.Interior.Color=RGB(r,g,b))=False

combo

 

[comaboy]

heh, Obviously Excel will palletise the RBG, but I'm not really expecting the macro to run on an 8-bit display ;-)

... and as I said "This might help you. It's a quick'n'dirty..."

:-D