VBA: Color Reference For Colorindex

August 18th, 2004 | Categories: VBA | Tags: , ,

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

ColorReference


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

Get LIVE Help
Custom Excel solutions & live Excel help!
► Go to www.ExcelAutomationHelp.com
► Email Now@ExcelAutomationHelp.com
  1. July 31st, 2008 at 17:03
    Reply | Quote | #1

    Thanks, works great.

    However, Why does excel give 56 numbers to 35 colors?

    Thanks again.

  2. Les Z
    January 16th, 2009 at 19:45
    Reply | Quote | #2

    thanks for this. simple but very helpful.

  3. Miguel
    May 21st, 2009 at 14:51
    Reply | Quote | #3

    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!

    • WillMC
      February 7th, 2010 at 02:54
      Reply | Quote | #4

      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

  4. SarikaG
    February 18th, 2010 at 18:28
    Reply | Quote | #5

    This was very helpful, Thanks for sharing.

  5. Arrif
    May 4th, 2010 at 14:29
    Reply | Quote | #6

    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

  6. Nik
    August 9th, 2010 at 03:35
    Reply | Quote | #7

    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.

  7. October 4th, 2010 at 01:08
    Reply | Quote | #8

    What a superb design you have. your blog articles are pretty helpful too. Thank you :-D

  8. Nasir
    October 12th, 2010 at 07:48
    Reply | Quote | #9

    Dear Sir;

    Many many thanks for excellant professional macro

    Good Luck and best wishes

  9. February 16th, 2011 at 15:04

    Thanks a lot,dis is of great help

  10. June 22nd, 2011 at 16:21

    That really helped, Thanks for the color pallette!!!!

  11. eros
    July 16th, 2011 at 21:14

    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.

  12. Overnighter
    November 17th, 2011 at 15:35

    Thanks for posting!! Awesome!

  13. 1 trackbacks