|
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 |
Color Row Macro Problem, adapted from Patrick Malloy macro
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- |
Color Row Macro Problem, adapted from Patrick Malloy macro
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" 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- |
Color Row Macro Problem, adapted from Patrick Malloy macro
Its under "SpecialCells" in Excel help.
Try...SpecialCells(xlCellTypeConstants, xlTextValues) -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "SteveC" 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" 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- |
Color Row Macro Problem, adapted from Patrick Malloy macro
Jim, Works great now, thanks very much!
|
| All times are GMT +1. The time now is 03:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com