Color Row Macro Problem, adapted from Patrick Malloy macro

[SteveC]

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

 

[Jim Cone]

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-

 

[SteveC]

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]

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...

 

[SteveC]

Jim, Works great now, thanks very much!