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.