Results 1 to 7 of 7

Thread: Using Colour Codes in an array

  1. #1
    New Member
    Join Date
    Dec 2011
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using Colour Codes in an array

    Hi,
    I have a table that stores the field name in column 1 then the colour code in column 2 (see below) in the VBA code I am trying to use the Colour code. If I use it as a number it works but I can't work out the correct number for the RGB codes I have. How do I pass the array variable?

    Column 1 Column 2 RGB Code
    Coxes Apples 16225862 247,150,70
    Bramley Apples 16225862 247,150,70
    Commice Pears 1515077 23,30,69
    Conference Pears 1515077 23,30,69

    The Array of Column 1 and Column 2 is called arrAssetClass

    Range("B" & Rowcount).Interior.Color = arrAssetClass(AssetClassRow, 2)

    Any ideas how I can get the correct number code or pass the RGB or Hex code?
    Thanks,
    Mike

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,190
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Using Colour Codes in an array

    Mike

    If the values in column 2 are valid colour codes then they should work.

    If you wanted to use the string in the RGB code column you could split it and try something like this.
    Code:
    arrRGB = Split(arrAssetClass(AssetClassRow,3), ",")
    
    Range("B" & Rowcount).Interior.Color = RGB(arrGRB(0),arrGRB(1),arrGRB(2))
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Dec 2011
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Colour Codes in an array

    Quote Originally Posted by Norie View Post
    Mike

    If the values in column 2 are valid colour codes then they should work.

    If you wanted to use the string in the RGB code column you could split it and try something like this.
    Code:
    arrRGB = Split(arrAssetClass(AssetClassRow,3), ",")
    
    Range("B" & Rowcount).Interior.Color = RGB(arrGRB(0),arrGRB(1),arrGRB(2))
    Thanks for your help and reply - Column 2 are valid colour codes but I can't find the correct number that corresponds to the RGB code in Column 3. Any ideas how I can find the correct numerical code?

  4. #4
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,669
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Using Colour Codes in an array

    If i colour some cells using 16225862 and RGB(247,150,70) they arent the same colour. RGB(70,150,247) is though.
    Looking for opportunities

  5. #5
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,669
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Using Colour Codes in an array

    Heres a method once you have sorted out the order issue:

    Code:
    With Sheets("Sheet1")
        lr = .Cells(.Rows.Count, "C").End(xlUp).Row
        arr = .Range("C2:D" & lr)
        For i = LBound(arr) To UBound(arr)
            R = Split(arr(i, 1), ",")(0)
            G = Split(arr(i, 1), ",")(1)
            B = Split(arr(i, 1), ",")(2)
            arr(i, 2) = Int("&H" & Hex(RGB(R, G, B)))
        Next
        .Range("D2:D" & lr) = Application.Index(arr, 0, 2)
    End With
    This places the number in column D based on the RGB in column C. Is that what you want?
    Looking for opportunities

  6. #6
    New Member
    Join Date
    Dec 2011
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Colour Codes in an array

    Thanks Norie - used this code and worked perfectly. Also very simple and only a couple of lines of code..... Many thanks for your help.

  7. #7
    New Member
    Join Date
    Dec 2011
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Colour Codes in an array

    Steve the fish - Thanks for your time and help with my query.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •