PDA

View Full Version : Cells Interior Color



Tiger Chen
09-10-2006, 10:42 PM
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


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
09-10-2006, 11:15 PM
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
09-10-2006, 11:20 PM
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
09-11-2006, 12:35 AM
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
09-11-2006, 01:00 AM
have a look here http://www.relief.jp/itnote/archives/000482.php

ALe
09-11-2006, 01:01 AM
OR http://www.mvps.org/dmcritchie/excel/colors.htm

Tiger Chen
09-11-2006, 01:05 AM
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


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
09-11-2006, 01:17 AM
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
09-11-2006, 01:21 AM
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
09-11-2006, 01:32 AM
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
09-11-2006, 01:36 AM
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
09-11-2006, 02:23 AM
Excel is stupid, but we are smart :)

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


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

mdmackillop
09-11-2006, 02:24 AM
BTW, For your original code,
try

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

Tiger Chen
09-11-2006, 02:31 AM
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
09-11-2006, 02:49 AM
:thumb