Creating Color Row "Control Panel"
- From: SteveC <SteveC@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 21 Jun 2006 06:23:02 -0700
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
.
- Prev by Date: Re: Macro launches VB IDE window
- Next by Date: Re: SendMail Error Trapping
- Previous by thread: 'Do Until...' Restart
- Next by thread: RE: Creating Color Row "Control Panel"
- Index(es):