Color cells bsed on table of RGB numbers

[nicksoph]

Hi
Im using Excel 2007 and have a table of 3 colums: Each row gives a colours RGB values in numbers from 1 to 255. I would like the 4th row to display the color based upon the numbers in the first cells.

Any help would be appreciated.

Best wishes
Nic

 

[Bill Rockenbach]

Place this code in the sheet code module. It will change the color in column D as RBG color numbers are intered into columns A,B and c.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim row As Long
If Not Intersect(Target, Range("A:C")) Is Nothing Then
row = Target.row
If Target < 0 Or Target > 255 Then
MsgBox "An invalid numbr as been intered to cell " & Target.Address
Application.Undo
End If
Cells(row, 4).Interior.Color = RGB(Cells(row, 1), Cells(row, 2), Cells(row, 3))
End If
End Sub

If you want to create the colors for all rows in a loop then use tis code in the worksheet code module

 

[Legacy 68668]

Bill

Using "row" as a variable name is not recommended since Row is a reaserved word for VBA.

And you will need to dim as Row, so that the row property becomes Row.

row = Target.row

 

[Bill Rockenbach]

Corrected code
Since excel can only use colors that are on the color palette, when you change a cell by RGB excel chooses the closest match on the color pallet. To achieve the exact RGB color the color palette has to be change. This will produce the exact RGB color for the selected cell, but all other cells using the color pallet item whose color was changed will reflect that color change.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lRow As Long
    If Not Intersect(Target, Range("A:C")) Is Nothing Then
        lRow = Target.Row
        If Target < 0 Or Target > 255 Then
            MsgBox "An invalid number has been entered to cell " & Target.Address
            Application.Undo
        End If
        ActiveWorkbook.Colors(2) = RGB(Cells(lRow, 1), Cells(lRow, 2), Cells(lRow, 3))
        Cells(lRow, 4).Interior.ColorIndex = 2
        'ActiveWorkbook.Colors(2) = RGB(255, 255, 255)
    End If
End Sub

Seiya,
I aggree, just get sloppy sometimes. Not too professional.

 

[nicksoph]

Thanks for the replies, Im stunned by how quickly forum questions are answered.

I didnt express my level of ignorance. "Option Explicit" - User Clueless

I have tried to find soloutions to my ignorance on the and so far have;

saved the workbook as an xlsm and enabled macros

Right clicked the sheet tab selected "View code" and pasted Bill's 2nd code sample into the VB editor - saved this and then gone back to my sheet.

Now when I change the values in colums a b or c the colours in coloum d all change to one colour rather than just the row changed.

looking at the code in the Vb editor I see
'ActiveWorkbook.Colors(2) = RGB(255, 255, 255) - is highlighted in green

Any help appreciated,

Best wishes
Nic

 

[Bill Rockenbach]

Option Explicit means that you have to Dimension your variables or you will get a run time error for those variables not dimensioned. Recommend always doing this to prevent spelling variables inconsistently causing unpredictable results.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

This will produce the exact RGB color for the selected cell, but all other cells using the color pallet item whose color was changed will reflect that color change

<o></o>
<o></o>
Read the fine print.<o></o>
<o></o>
Either you get a color off the palette that is close to the RGB specs in your columns as in my first code posted when the cell is directly assigned the RBG numbers or you change the color pallet color and all cells assigned that color will change. <o></o>
<o></o>
You could program a different color on the color palette for each row, but you are limited to the number of color cells in the color pallet. <o></o>
How many rows do you have??

 

[nicksoph]

Thanks Bill

It may be that Im not going to be able to what I want using Excel.

I have a spreadsheet that gives the x,y co-ordinates and radius for about about 10,000 circles. This spreadsheet is used to generate a text file which is used by CorelDraw to plot those circles. The spreadsheet can also pass on the colours for those circles. What I wanted to do was edit the numbers in Excel and be able to manipulte the colours and see the results instantly and without using a colour picker which is impractical for that many circles.

Thank you for your time.
best wishes
nic

 

[nicksoph]

To answer your question - I would wish to have a pallet of all RGB colours but would need one of at least 1000 colours
Nic

 

[Bill Rockenbach]

In versions of excel prior to xl2007 you are limite to 56 colors. I don't know about xl2007

 

[VoG]

Assuming that your table of RGB numbers is in columns A to C then the following will color column D accordingly.

First click the officer button

then click Excel Options, check Show Developer tab in the Ribbon and click OK.

Press ALT + F11 to open the Visual Basic Editor, then select Module from the Insert menu. Paste the following into the white space on the right:

Sub ColorMe()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("D" & i)
        .Interior.Color = RGB(.Offset(, -3).Value, .Offset(, -2).Value, .Offset(, -1).Value)
    End With
Next i
End Sub

Press ALT + Q to return to your sheet. On the Developer tab click Macros, click on ColorMe and click the Run button.