OzGrid

How to use VBA code to find color index which is not found on excel color palette

Requirement:

The user has colour format that is from another spreadsheet which is not the users.

Could use paste special format but the user is trying to know the color index code of these colors as per attached sheet as it cannot be found on excel colour palette.

The user would like to have the color code so that the user could apply it in macros.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/148551-vba-to-find-color-index-which-is-not-found-on-excel-color-palette

Solution:

You can have the following function on a Standard Module...

Function getColorIndex(Cell As Range)
    getColorIndex = Cell.Interior.ColorIndex
End Function

And then you can use it in a code like this...

Sub ApplyCellColor()
Range("A10").Interior.ColorIndex = getColorIndex(Range("A1"))
End Sub

Or you can declare a variable to hold the colorindex and then use it like this...

Sub ApplyCellColor()
Dim clrIdx As Long
clrIdx = getColorIndex(Range("A1"))

Range("A10").Interior.ColorIndex = clrIdx
End Sub

KjBox provides the following solution:

The code above is limited to colours that are in the ColorIndex Pallet. Change A1 to a different colour, say RGB 255, 217, 102 and you will find that cell A10 does not have the same interior colour as A1.

For the full range of 64 million or so colours, including theme colour tints and shades, use:

Sub ApplyCellColor2()
    [A10].Interior.Color = GetInteriorColor([a1])
End Sub

Function GetInteriorColor(r As Range)
    GetInteriorColor = r.Interior.Color
End Function

Or you could do away with the function and just use

Sub ApplyCellColor3()
    [a10].Interior.Color = [a1].Interior.Color
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by sktneer and KjBox.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery