<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Return Excel Color Index Number or Color as Text

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

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.comSelect 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 IfEnd Function

To use this UDF push Alt+F11 and go Insert>Module and paste in the code. PushAlt+Q and save. The Function will appear under "UserDefined" 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

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

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

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

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