none
Excel Cell Color - Set Cell RGB Interior RRS feed

  • Question

  • hi Friends,

    I am trying to set excel cell colors,

    In column A , I have put the RGB colors

    A           

    5,0,251
    0,250,0

    In column B -  I want to display the interior cell color

    So far I have this

    Sub SetCellColor()

    Dim ws   As Worksheet
    Dim i As Long
    Dim iRGB As Long
    Dim iRGBs As Variant

    Set ws = Worksheets("RGB")
    For i = 2 To 7
        
        
         iRGBs = Split(ws.Range(i, 1).Value, ",")
         iRGB = rgb(Trim(iRGBs(0)), Trim(iRGBs(1)), Trim(iRGBs(2)))
         iRGB = ws.Cells(i, 2).Interior.Color
        

    Next i
    End Sub

    The worksheet failed, and I have no idea why and been at this for a long time :(

    any advice on whats gone wrong

    thank you

    pw
    Thursday, June 30, 2016 12:39 AM

Answers

  • Use this code:

    Option Explicit
    
    Sub SetCellColor()
        Dim ws As Worksheet
        Dim iColor As Long
        Dim iRGBs As Variant
        Dim i As Long
        
        Set ws = Worksheets("Test")
        
        For i = 2 To 4
            iRGBs = Split(ws.Cells(i, "A").Value, ",")
            iColor = RGB(iRGBs(0), iRGBs(1), iRGBs(2))
            ws.Cells(i, "B").Interior.Color = iColor
        Next i
    End Sub
    

    I got tricked by:

    iRGBs = Split(ws.Range(i, 1).Value, ",")
    The correct is Cells.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    • Marked as answer by Dan_CS Thursday, June 30, 2016 5:47 PM
    Thursday, June 30, 2016 5:31 PM

All replies

  • You have 1 line backwards

    iRGB = ws.Cells(i, 2).Interior.Color

    should be

    ws.Cells(i, 2).Interior.Color = iRGB

    Thursday, June 30, 2016 1:58 AM
  • Hello  MM,

    I just tested that and still the error worksheet object failed :(

    I will keep testing

    thank you

    Thursday, June 30, 2016 2:09 AM
  • Do you have a workbook named RGB? Where does the error occurs?

    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Thursday, June 30, 2016 2:24 AM
  • Hello Felipe,

     iRGBs = Split(ws.Range(i, 1).Value, " ")

    Worksheet object failed

    its always the same line, but I am not sure why?

    Thursday, June 30, 2016 2:29 AM
  • What does it happen when you do like this:

    Sub SetCellColor()
        Dim ws   As Worksheet
        Dim i As Long
        Dim iRGB As Long
        Dim iRGBs As Variant
        
        Set ws = Worksheets("RGB")
        For i = 2 To 7
             MsgBox ws.Range(i, 1).Value
             iRGBs = Split(ws.Range(i, 1).Value, ",")
             iRGB = RGB(Trim(iRGBs(0)), Trim(iRGBs(1)), Trim(iRGBs(2)))
             iRGB = ws.Cells(i, 2).Interior.Color
        Next i
    End Sub
    
    
    ?


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Thursday, June 30, 2016 12:02 PM
  • Hi Felipe,

    it says method range of worksheet failed

    Runtime error 1004

    MsgBox ws.Range(i, 1).Value

    I changed the worksheet name to Test, just in case there was a clash

    thank you

    pw

    Thursday, June 30, 2016 12:30 PM
  • It is very strange the fact that Set ws = Worksheets("RGB") is OK, but ws.Range(i, 1).Value raises an error.

    This can happen only if ws.Range(i, 1).Value is an error, but it would be error 13.

    I ran out of ideas, could you share your workbook?


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Thursday, June 30, 2016 2:04 PM
  • Hello Felipe,

    sorry this is being a pain 

    thanks for your help

    pw


    • Edited by Dan_CS Thursday, June 30, 2016 5:49 PM
    Thursday, June 30, 2016 2:57 PM
  • Use this code:

    Option Explicit
    
    Sub SetCellColor()
        Dim ws As Worksheet
        Dim iColor As Long
        Dim iRGBs As Variant
        Dim i As Long
        
        Set ws = Worksheets("Test")
        
        For i = 2 To 4
            iRGBs = Split(ws.Cells(i, "A").Value, ",")
            iColor = RGB(iRGBs(0), iRGBs(1), iRGBs(2))
            ws.Cells(i, "B").Interior.Color = iColor
        Next i
    End Sub
    

    I got tricked by:

    iRGBs = Split(ws.Range(i, 1).Value, ",")
    The correct is Cells.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    • Marked as answer by Dan_CS Thursday, June 30, 2016 5:47 PM
    Thursday, June 30, 2016 5:31 PM
  • Thank you Felipe, 

    :)

    what a wonderful person you are to always help.

    I thought it was me being dim, and not being able to sort it out  - i tried everything backwards and forwards and added more and deleted yikeeees!

    thanks again my dear friend

    have a great day :

    pw

    Thursday, June 30, 2016 5:47 PM