Using "Type" instead of Enum as had xl97 open.
Personally, I would get rid of the Types or Enums and just use
visual basic constants for the colors...
vbWhite, vbGreen, vbRed, vbBlack, vbYellow, vbBlue, vbMagenta,
vb Cyan
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
Type Colors
green As Long
yellow As Long
blue As Long
White As Long
End Type
Sub Update_Report_Colors()
Dim sheet As Worksheet
Dim i As Integer
Dim keycol As Long
Dim cell As Range
Dim found As Range
Dim MyColor As Colors
Dim color As Long
Set sheet = Worksheets("HotList")
MyColor.green = 35
MyColor.yellow = 36
MyColor.blue = 34
MyColor.White = 2
keycol = 2
With sheet
Set found = .Columns(keycol).SpecialCells(xlCellTypeFormulas)
For Each cell In found
Select Case cell.Value
Case "Advertising"
color = MyColor.green
Case "Apparel Retail"
color = MyColor.yellow
Case "Apparel, Accessories and Luxury Goods"
color = MyColor.blue
Case "Auto Components"
color = MyColor.green
Case "Auto Parts and Equipment"
color = MyColor.yellow
Case "Automobile Manufacturers"
color = MyColor.blue
Case "Automobiles"
color = MyColor.green
Case "Automobiles and Components"
color = MyColor.yellow
Case "Automotive Retail"
color = MyColor.blue
Case "Broadcasting and Cable TV"
color = MyColor.green
'About 200 more cases and then...
Case Else
color = MyColor.White
End Select
With .Range(.Cells(cell.Row, "A"), .Cells(cell.Row, "Z"))
.Interior.ColorIndex = color
End With
Next
End With
End Sub
'--------------
"SteveC"
wrote in message
I'm trying to adapt a macro by Patrick Malloy MVP, and this is what I now
have. It's not working. Wondering if anyone has any suggestions.
At first try nothing happened. Then I changed
With .Range(.Cells(cell.Row, "A"), .Cells(cell.Row, "Z"))
to
With .Range(.Cells(cell.Row, "A1"), .Cells(cell.Row, "Z3000"))
Second try now I get an error:
Compile error: Constant expression required
with the debugger highlighting "green" in
Case "Advertising"
color = Colors.green
Any suggestions? Thanks very much!
-snip-