Return RGB values from Range.Interior.Color (or any other Color Property)

by

That “arbitrary” number is a mathematical combination of the RGB values (B256^2 + G256 + R) and a conversion of the hex color value to a decimal number (base 16 to base 10), depending on which way you want to look at it. Just different bases. Below is the method I use in the XLAM addin file I wrote for Excel. This method has come in handy many times. I have included the documentation in my addin file.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'   Function            Color
'   Purpose             Determine the Background Color Of a Cell
'   @Param rng          Range to Determine Background Color of
'   @Param formatType   Default Value = 0
'                       0   Integer
'                       1   Hex
'                       2   RGB
'                       3   Excel Color Index
'   Usage               Color(A1)      -->   9507341
'                       Color(A1, 0)   -->   9507341
'                       Color(A1, 1)   -->   91120D
'                       Color(A1, 2)   -->   13, 18, 145
'                       Color(A1, 3)   -->   6
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function Color(rng As Range, Optional formatType As Integer = 0)     As Variant
    Dim colorVal As Variant
    colorVal = rng.Cells(1, 1).Interior.Color
    Select Case formatType
        Case 1
            Color = WorksheetFunction.Dec2Hex(colorVal, 6)
        Case 2
            Color = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
        Case 3
            Color = rng.Cells(1, 1).Interior.ColorIndex
        Case Else
            Color = colorVal
    End Select
End Function

More Related Contents:

  1. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  2. Excel Number Separation [closed]
  3. excel vba compare & copy worksheets from 2 excel files
  4. VBA Stuck after certain loop
  5. How do I use FileSystemObject in VBA?
  6. Is the . in .Range necessary when defined by .Cells?
  7. How to return a result from a VBA function
  8. Cycle through sub-folders and files in a user-specified root directory [duplicate]
  9. Run Excel Macro from Outside Excel Using VBScript From Command Line
  10. Importing text file into excel sheet
  11. Filling any empty cells with the value above
  12. Properly Handling Errors in VBA (Excel)
  13. Password hash function for Excel VBA
  14. hiding formulas in formula bar
  15. Excel VBA: getting row of clicked button [duplicate]
  16. VBA Many buttons point to the same _Click sub
  17. Excel VBA Filter Change event handler
  18. How to copy only values in excel vba from a range?
  19. How to suppress Update Links warning?
  20. How do we use restful APIs from Excel macros (vba)?
  21. Cannot VBA write data to cells in Excel 2007/2010 within a function
  22. Excel: the Incredible Shrinking and Expanding Controls [closed]
  23. How to send email to multiple recipients with addresses stored in Excel?
  24. What are the benefits of using Classes in VBA? [closed]
  25. Writing a VLOOKUP function in vba
  26. Handle cancellation of InputBox to select range
  27. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  28. VBA: How to combine two ranges on different sheets into one, to loop through
  29. How to protect Excel workbook using VBA?
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment