VBA: Color Reference For Colorindex
When using VBA to code the Colorindex (or background color) of a cell it is useful to know what integer will equal what color. Below is a reference picture which shows the color and lists it’s respective Colorindex. aka VBA Color Palette
Here’s the code to make one for yourself, or just bookmark the permalink:
Sub ColorRef() Dim x As Integer For x = 1 To 56 If x < 29 Then Cells(x, 1).Interior.ColorIndex = x Cells(x, 2) = x Else Cells(x - 28, 3).Interior.ColorIndex = x Cells(x - 28, 4) = x End If Next x End Sub
Thanks, works great.
However, Why does excel give 56 numbers to 35 colors?
Thanks again.
thanks for this. simple but very helpful.
I would like to biuld a macro to delete a whole row if the color of the cell in row X is red. Can you help me with this?
I tried this
Sub Delete_Low_Volume()
Dim WatchRange As Range
Dim CellTest As Range
Set WatchRange = Range(“X:X”)
For Each CellTest In WatchRange.Cells
If ActiveCell.Interior.ColorIndex = 4 Then
ActiveCell.EntireRow.Delete
‘ActiveCell.Offset(0, -1).Range(“A1″).Select
End If
Next CellTest
End Sub
but it is deleting me everything. all the rows. I don’t know if it may be because of the fact that I’m using conditional formating with cells in row X.
Thanks!
This was fast and dirty. Simple to use and debug.
Sub colordel()
Do
Range(“A1″).Select
‘ Looks for a blank cell and stops the loop.
ender = ActiveCell.Value
If ActiveCell.Interior.ColorIndex = 4 Then GoTo 12
ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
12
ActiveCell.Offset(1, 0).Select
Loop Until ender = “”
End Sub
This was very helpful, Thanks for sharing.
I’m trying to build a macro which will change the colour of the cells if it is particular colour (say red)for a specific range of cell (say A1:D8).can you please help
Too bad red isn’t one of the duplicate colours, I’m looking for a way to differentiate between a cell who’s font was marked red by the user and one marked red programatically.
What a superb design you have. your blog articles are pretty helpful too. Thank you
Dear Sir;
Many many thanks for excellant professional macro
Good Luck and best wishes
Thanks a lot,dis is of great help
That really helped, Thanks for the color pallette!!!!
But “Cells(x, 1).Interior.ColorIndex” don’t work if I use a conditional formatting !!
Anybody have any idea to determine the color about cells with conditional formatting ?
thks.
Thanks for posting!! Awesome!