Creating Color Row "Control Panel"



In work*** "AllCos", ColumnB, I have a list of names,

In work*** "HList", A13:Z3000 I have data I want to color format by row.

ColB in "HList" contains names listed in ColB "AllCos".

I would like a macro to copy the formatting in ColB "AllCos", and apply it
to the rows of "HList" by matching the values in ColB AllCos to ColB HList.

For example:

*** "AllCos"
ColB
Pears the background color of this cell is yellow
Apples the background color of this cell is blue
Bannanas the background color of this cell is green


*** "HList"
ColB
Apples This row A:AS is colored blue, because it is applying format from
"AllCos"
Apples This row A:AS is colored blue, as above
Pears This row A:AS is colored yellow
etc...

separately,
This may or may not be relevant: thanks to Jim Cone and Patrick Malloy, I
have a macro now that applies row color formatting according to preset
definitions. However, now I'd like to have flexibility in modifynig the
color formats as I explained above. I've reposted it below. I hope it's
relevant.

Even better:
the macro above -- it woudl be great if it would apply all formatting found
in ColB Allcos to the rows in HList, not just color -- that would be super.

Thanks for your help.

Type Colors
green As Long
yellow As Long
blue As Long
White As Long
End Type

Sub Update_Report_Colors()
Dim *** As Work***
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 ***
Set found = .Columns(keycol). SpecialCells(xlCellTypeConstants, xlTextValues)

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

.