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.
Solution:
You can have the following function on a Standard Module...
Code:
Function getColorIndex(Cell As Range)
getColorIndex = Cell.Interior.ColorIndex
End Function
And then you can use it in a code like this...
Code:
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...
Code:
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:
Code:
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
Code:
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:
| How to use VBA code to colour tabs based on tab/text number |
| How to loop each row if there is data after green colour cell then delete |
| How to loop each row if there is data after green colour cell then delete |
| How to change cell colour if row contains color |
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
