Excel VBA Custom Function to Get RGB Color

This tutorial explains how to create an Excel VBA custom Function to get RGB Color using Interior.Color Property.

Excel VBA Custom Function to Get RGB Color

RGB is a VBA Property that can set RGB color. Usually Property comes in a pair, one Method is to Set Property and another isĀ  Get Property. However, RGB does not seem to have the Get Method, therefore you cannot get the RGB color directly.

To get the RGB Color, we need to make use Interior.Color Property to get Interior Color code, which is a sum of RGB color with some calculations (see below). After getting the Interior Code, we can convert to RGB color using the below formula

Interior Color = red+ green * 256 + blue * 256 ^ 2

Note carefully that if a Cell is colorless (no color), Interior.Color would return value 16777215, which is also the white color code.

VBA code of Custom Function – Get RGB Color

Below is a custom Function to get RGB color, where r, g, b are RGB value of Red, Green, Blue

Public Function wRGB(rng As Range)
 Dim intColor As Long
 Dim rgb As String
 intColor = rng.Interior.Color
 r = intColor And 255
 g = intColor \ 256 And 255
 b = intColor \ 256 ^ 2 And 255
 wRGB = r & "," & g & "," & b
End Function

Syntax of Custom Function – Get RGB Color

wRGB(rng)

rng is the colored Cell which you want to get its RGB color

Example of Custom Function – Get RGB Color

RGB

Recommended Readings

VBA Excel RGB Property and get RGB Color

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *