cells.interior.color values

[Matthew Dyer]

All tests done in Excel 2007:

So i'm trying to make sense of this particular setting. I understand
the color index has 50-some-odd possibilities, which severely limits
the various iterations of color possible by Excel. So i came up with
the below code, which runs till it hits too many cell formats

Sub colortest()
Application.ScreenUpdating = False
Dim r As Double
Dim c As Double
Dim x As Double
x = 255
For c = 1 To 9999
For r = 1 To 255
With Cells(r, c)
.Interior.Color = x
.Value = x
End With
x = x + 255
Next r
Next c
Application.ScreenUpdating = True
End Sub

I go up in interations of 255 per hex values, but i'm still a bit in
the dark as to why it works this way.

This code works properly for a total of 65429 cells, but then on cell
65430 it says i've reached maximum number of possible cell formats (c
= 257, r = 150, x = 16684650).

Could anyone explain how excel takes a numerical value for the
interiors.color value and determines what the output color will be?

 

[isabelle]

hi Matthew,

Sub test()
TheColor = ActiveCell.Interior.Color

Red = Int(TheColor Mod 256)
Green = Int((TheColor Mod 65536) / 256)
Blue = Int(TheColor / 65536)

MsgBox "Color " & TheColor & Chr(10) & _
"RED : " & Red & Chr(10) & _
"GREEN : " & Green & Chr(10) & _
"BLUE : " & Blue
End Sub

 

[isabelle]

sorry I forgot to include the method for applying this color
and select a cell with a color before you run the macro


Sub test()
TheColor = ActiveCell.Interior.Color

Red = Int(TheColor Mod 256)
Green = Int((TheColor Mod 65536) / 256)
Blue = Int(TheColor / 65536)

MsgBox "Color " & TheColor & Chr(10) & _
"RED : " & Red & Chr(10) & _
"GREEN : " & Green & Chr(10) & _
"BLUE : " & Blue

ActiveCell.Offset(, 1).Interior.Color = RGB(Red, Green, Blue)

End Sub

 

[Matthew Dyer]

sorry I forgot to include the method for applying this color
and select a cell with a color before you run the macro

Sub test()
TheColor = ActiveCell.Interior.Color

Red = Int(TheColor Mod 256)
Green = Int((TheColor Mod 65536) / 256)
Blue = Int(TheColor / 65536)

MsgBox "Color " & TheColor & Chr(10) & _
"RED : " & Red & Chr(10) & _
"GREEN : " & Green & Chr(10) & _
"BLUE : " & Blue

ActiveCell.Offset(, 1).Interior.Color = RGB(Red, Green, Blue)

End Sub

Thanks isabelle. That code is most excellent!