FREE Excel STUFF

Search

Excel Help. Popular

NEW! Multiple Excel Search & Links

Excel Formulas

Excel Macros

Excel Newsletter

PRODUCTS

Up to $139.00 FREE!

Categories & Search

Excel Templates

Excel Add-ins

Excel Training

More....

OTHER

Excel Development

Return Excel Color Index Number or Color as Text

NEW! More Books..

advanced search! Free Help! Add Excel Answers & Search To Your Google Toolbar Details

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Back to: Excel Custom Function/Formulas . Got any Excel/VBA Questions? Free Excel Help

This UDF will return the cells fill color as either an Index number (default) or it's color name as text

The Code

Function CellColor(rCell As Range, Optional ColorName As Boolean)
Dim strColor As String, iIndexNum As Integer

'Written by Dave Hawley of OzGrid.com
Select Case rCell.Interior.ColorIndex
   Case 1
    strColor = "Black"
    iIndexNum = 1
   Case 53
    strColor = "Brown"
    iIndexNum = 53
   Case 52
    strColor = "Olive Green"
    iIndexNum = 52
   Case 51
    strColor = "Dark Green"
    iIndexNum = 51
   Case 49
    strColor = "Dark Teal"
    iIndexNum = 49
   Case 11
    strColor = "Dark Blue"
    iIndexNum = 11
   Case 55
    strColor = "Indigo"
    iIndexNum = 55
   Case 56
    strColor = "Gray-80%"
    iIndexNum = 56
   Case 9
    strColor = "Dark Red"
    iIndexNum = 9
   Case 46
    strColor = "Orange"
    iIndexNum = 46
   Case 12
    strColor = "Dark Yellow"
    iIndexNum = 12
   Case 10
    strColor = "Green"
    iIndexNum = 10
   Case 14
    strColor = "Teal"
    iIndexNum = 14
   Case 5
    strColor = "Blue"
    iIndexNum = 5
   Case 47
    strColor = "Blue-Gray"
    iIndexNum = 47
   Case 16
    strColor = "Gray-50%"
    iIndexNum = 16
   Case 3
    strColor = "Red"
    iIndexNum = 3
   Case 45
    strColor = "Light Orange"
    iIndexNum = 45
   Case 43
    strColor = "Lime"
    iIndexNum = 43
   Case 50
    strColor = "Sea Green"
    iIndexNum = 50
   Case 42
    strColor = "Aqua"
    iIndexNum = 42
   Case 41
    strColor = "Light Blue"
    iIndexNum = 41
   Case 13
    strColor = "Violet"
    iIndexNum = 13
   Case 48
    strColor = "Gray-40%"
    iIndexNum = 48
   Case 7
    strColor = "Pink"
    iIndexNum = 7
   Case 44
    strColor = "Gold"
    iIndexNum = 44
   Case 6
    strColor = "Yellow"
    iIndexNum = 6
   Case 4
    strColor = "Bright Green"
    iIndexNum = 4
   Case 8
    strColor = "Turqoise"
    iIndexNum = 8
   Case 33
    strColor = "Sky Blue"
    iIndexNum = 33
   Case 54
    strColor = "Plum"
    iIndexNum = 54
   Case 15
    strColor = "Gray-25%"
    iIndexNum = 15
   Case 38
    strColor = "Rose"
    iIndexNum = 38
   Case 40
    strColor = "Tan"
    iIndexNum = 40
   Case 36
    strColor = "Light Yellow"
    iIndexNum = 36
   Case 35
    strColor = "Light Green"
    iIndexNum = 35
   Case 34
    strColor = "Light Turqoise"
    iIndexNum = 34
   Case 37
    strColor = "Pale Blue"
    iIndexNum = 37
   Case 39
    strColor = "Lavendar"
    iIndexNum = 39
   Case 2
    strColor = "White"
    iIndexNum = 2
  Case Else
    strColor = "Custom color or no fill"
End Select

	If ColorName = True Or _
		strColor = "Custom color or no fill" Then
		CellColor = strColor
	Else
		CellColor = iIndexNum
	End If

End Function

To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function dialog box (Shift+F3). Use the Function in any cell as shown below.

=CellColor(A1,TRUE)
Where cell A1 has a fill color of dark red the result would be "Dark Red"

=CellColor(A1,FALSE) OR CellColor(A1)
Where cell A1 has a fill color of dark red the result would be 9

See Also: Excel Duplication Manager Add-in | Excel Number Manager Add-in | Excel Text Manager Add-in | Excel Named Range Add-in Manager | Excel OzGrid Plus Add-in | Excel Time Sheet | Excel Time Wage and Pay book

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Search Tips FREE Excel Help

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft