Color Row Macro Problem, adapted from Patrick Malloy macro

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!

Option Explicit
Enum Colors
green = 35
yellow = 36
blue = 34
White = 2
End Enum
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 color As Long
Set sheet = Worksheets("HotList")

keycol = 2

With sheet
Set found = .Columns(keycol).SpecialCells(xlCellTypeFormulas)
For Each cell In found
Select Case cell.Value

Case "Advertising"
color = Colors.green

Case "Apparel Retail"
color = Colors.yellow

Case "Apparel, Accessories and Luxury Goods"
color = Colors.blue

Case "Auto Components"
color = Colors.green

Case "Auto Parts and Equipment"
color = Colors.yellow

Case "Automobile Manufacturers"
color = Colors.blue

Case "Automobiles"
color = Colors.green

Case "Automobiles and Components"
color = Colors.yellow

Case "Automotive Retail"
color = Colors.blue

Case "Broadcasting and Cable TV"
color = Colors.green

'About 200 more cases and then...

Case Else
color = Colors.White
End Select

With .Range(.Cells(cell.Row, "A1"), .Cells(cell.Row, "Z3000"))
.Interior.ColorIndex = color
End With
Next

End With

End Sub

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"
<[email protected]>
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-

Thanks Jim.

Still not working though. I pretty much a VBA noob, but I think the problem
is this line:

Set found = .Columns(keycol).SpecialCells(xlCellTypeFormulas)

The values I'm looking for are hard coded text values in Column B, not
formulas. I tried CellTypeValues but of course that's not it. I tried excel
help but I couldn't find an answer.

Thanks for your time...


"Jim Cone" wrote:

> 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"
> <[email protected]>
> 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-
>

Its under "SpecialCells" in Excel help.

Try...SpecialCells(xlCellTypeConstants, xlTextValues)

--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


"SteveC" <[email protected]>
wrote in message
Thanks Jim.

Still not working though. I pretty much a VBA noob, but I think the problem
is this line:

Set found = .Columns(keycol).SpecialCells(xlCellTypeFormulas)

The values I'm looking for are hard coded text values in Column B, not
formulas. I tried CellTypeValues but of course that's not it. I tried excel
help but I couldn't find an answer.
Thanks for your time...


"Jim Cone" wrote:

> 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"
> <[email protected]>
> 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-
>

Jim, Works great now, thanks very much!