Cell-colouring macro

[daba]

.... I have 3 cells (vertical), that contain R,G,B values.

These "boxes" of the 3 cells is repeated 255 times, making 256 * 3 cells in total.

I want all 3 cells in each "box" to be coloured to those RGB values, whenever any one of them is changed.

I'm having a mental block at the moment, and don't know how to proceed.

I'm sure it must be striaght-forward, if someone could point me in the right direction.....

 

[Peter_SSs]

OK, so this manually setting the RGB values of the 3 vertical cells is getting tedious to the extreme, so am once again looking for a solution, macro or vba.

I think I can simplify things if I select the topmost of the 3 cells, and have a button ..

I would forget the button, and just double-click the top cell of the 3. This code would still need to go in the sheet's module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True
  With Target.Resize(3)
    .Interior.Color = RGB(.Cells(1).Value, .Cells(2).Value, .Cells(3).Value)
  End With
End Sub

 

[daba]

I would forget the button, and just double-click the top cell of the 3. This code would still need to go in the sheet's module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True
  With Target.Resize(3)
    .Interior.Color = RGB(.Cells(1).Value, .Cells(2).Value, .Cells(3).Value)
  End With
End Sub

Absolutely brilliant Peter, it does exactly what I want !!

Thank-you.....

 

[Peter_SSs]

Absolutely brilliant Peter, it does exactly what I want !!

Thank-you.....

Cheers. Glad it worked for you. Better than colouring them all manually!!

 

[pjmorris]

hi daba

If I've interpreted your query correctly this should work. If it doesn't, try and understand the code as both Peter and I have given you the core requirements; if you have any queries about particular elements feel free to ask. The challenge in both solutions is calculating which cells to colour.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nShft As Integer 'used to align the groups of cells
    Dim nRow As Integer  'used to hold target row (just to make code easier to read)
    Dim nCol As Integer  'used to hold target column (just to make code easier to read)
    Const rSt = 23 'first data row
    
    nRow = Target.Row
    nCol = Target.Column
    
    If nRow >= 23 And nRow <= 70 And (nCol Mod 2) = 0 Then
        nShft = 1 - ((nRow - rSt) Mod 3)
    
    
        Range(Cells(nRow - 1 + nShft, nCol), Cells(nRow + 1 + nShft, nCol)).Interior.Color = _
                            RGB(Target.Offset(-1 + nShft), Target.Offset(nShft), Target.Offset(1 + nShft))
    End If
End Sub

Hope this helps.

regards