Cells Interior Color

[Tiger Chen]

[VBA]Sub test()
Dim i As Integer

For i = 1 To 255
Cells(i, 1).Interior.Color = i
Cells(i, 2) = Cells(i, 1).Interior.Color
Next i
End Sub
[/VBA]

When I run the code above in Office 2003 or below, I cannot get expected color scale from black to red. Instead, I just get three kinds of color: 0, 128 and 255.

I know this code works in Office 2007.

Why? How can I make it works in 2003? Thanks in advance!

[venkat1926]

It is not exactly clear what you want . try this code

Sub coloring()

Dim i As Integer
Dim x
For i = 1 To 255
        Cells(i, 1).Interior.ColorIndex = RGB(i, 0, 0)
        x = Cells(i, 1).Interior.ColorIndex
        Cells(i, 2).Interior.ColorIndex = x
    Next i
End Sub

both cells in columns A and B will have same color. I plresume you want that. you can modify it as you like.

venkat

[Tiger Chen]

We cannot set interior colorindex to 57 or higher.

By the way, I would like to set the interior color just according to its RGB data.

[venkat1926]

If you use RGB I think you will not able to distinguish all the shaded . In many moonigtors you can distinguish between only a few colors .I wonder whether you can distiguish among 256 shades.

[ALe]

have a look here http://www.relief.jp/itnote/archives/000482.php

[ALe]

OR http://www.mvps.org/dmcritchie/excel/colors.htm

[Tiger Chen]

[VBA]Private Sub CommandButton1_Click()
Dim i As Long
Dim myCtrl As Control

For i = 0 To 255
With Me
Set myCtrl = .Controls.Add("Forms.Label.1")
With myCtrl
.Left = (i Mod 30) * 10
.Width = 10
.Height = 10
.Top = Int(i / 30) * 10
.BackColor = i
End With
End With
Next i
End Sub
[/VBA]

If put these in a userform, it works. I can get the labels with color change from black to red continuously.

Please see the picture below.

[ALe]

You can't do the same with cells. As far as I know you can set only the 57 colors or the property ColorIndex (of course you can use the property Color instead of ColorIndex to determine the colors using RGB)

[Tiger Chen]

OR http://www.mvps.org/dmcritchie/excel/colors.htm

ALe,
Thanks! The information you shared is very good for Excel color understanding, but it does not help for my question.

I would like Excel can tell the difference between any color. For example, color RGB(255,0,0) is different vs. RGB(254,0,0). But Excel cell interior color cannot differentiate it. (Excel 2007 can. Excel 2003 VBA userform backcolor can also. - See my post above pls.)

[ALe]

Tiger Chen, that is what I actually was trying to tell.

Depending on your needs, maybe you could use shapes in your sheets with the same size of cells.

[Bob Phillips]

ALe,
Thanks! The information you shared is very good for Excel color understanding, but it does not help for my question.

I would like Excel can tell the difference between any color. For example, color RGB(255,0,0) is different vs. RGB(254,0,0). But Excel cell interior color cannot differentiate it. (Excel 2007 can. Excel 2003 VBA userform backcolor can also. - See my post above pls.)

When you set a cell to a colour, Excel will adjust it to the closest colour within the colour palette. So RGB(255,0,0) and RGB(254,0,0) will most probably map onto the same colour within the palette.

It's a fact of life, Excel only supports 56 colours within the cell and font.

[Tiger Chen]

Excel is stupid, but we are smart

I can accept the below alternative methodI use shape filling color to reflect the RGB data.)

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer, _
iR As Integer, _
iG As Integer, _
iB As Integer
Dim sinLeft As Single, _
sinTop As Single, _
sinWid As Single, _
sinHei As Single

iRow = Target.Row
sinLeft = Cells(iRow, 4).Left
sinTop = Cells(iRow, 4).Top
sinWid = Cells(iRow, 4).Width
sinHei = Cells(iRow, 4).Height

If Target.Column > 3 Then Exit Sub

iR = Cells(iRow, 1).Value
iG = Cells(iRow, 2).Value
iB = Cells(iRow, 3).Value

On Error GoTo ErrHandler
ActiveSheet.Shapes("Row" & iRow).Fill.ForeColor.RGB = RGB(iR, iG, iB)
Application.EnableEvents = False
Cells(iRow, 5) = ActiveSheet.Shapes("Row" & iRow).Fill.ForeColor.RGB
Application.EnableEvents = True
Exit Sub

ErrHandler:
ActiveSheet.Shapes.AddShape(msoShapeRectangle, sinLeft, sinTop, sinWid, sinHei). _
Select
Selection.Name = "Row" & iRow
Target.Select
ActiveSheet.Shapes("Row" & iRow).Fill.ForeColor.RGB = RGB(iR, iG, iB)
Application.EnableEvents = False
Cells(iRow, 5) = ActiveSheet.Shapes("Row" & iRow).Fill.ForeColor.RGB
Application.EnableEvents = True
End Sub
[/VBA]

[mdmackillop]

BTW, For your original code,
try
[VBA]
Sub test()
Dim i As Integer

For i = 1 To 255
Cells(i, 1).Interior.ColorIndex = i
Cells(i, 2) = Cells(i, 1).Interior.ColorIndex
Next i
End Sub

[/VBA]

[Tiger Chen]

Tiger Chen, that is what I actually was trying to tell.

Depending on your needs, maybe you could use shapes in your sheets with the same size of cells.

ah, you already gave the advice! We are at the same page. Thanks!

[ALe]