|Subject:||Re: hot key for background color|
|Posted by:||Ken Johnson (KenCJohns…@gmail.com)|
|Date:||21 May 2006|
1. Insert a name (New_Color) by going Insert>Name>Define> Type
"New_Color" into the "Names in Workbook:" box at the top of the "Define
Name" dialog (without the speech marks)>delete everything from the
"Refers to:" box at the bottom then type in 40 then click the "Add"
The following code will be looking for this name and changing its value
whenever you click a little colored shape. The new value of this
New_Color name is determined by the color of the little shape that you
click. Therafter, any time you change a value on the sheet its fill
color wil be the same as the color of the little shape last clicked.
2. Draw 6 little circles (or any other autoshape). Give one of them a
white fill (this one will be used to turn off the cell color change
function). With the other five shapes change their fill color to the
five desired colors.
3. Copy the following two codes>Right click the sheet tab> select "View
Code" from the popup>Paste the code into the sheet's code module>Press
Alt + F11 to get back to the worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If [New_color] <> 2 Then
Target.Interior.ColorIndex = [New_color]
Public Sub change_cell_color()
Dim nName As Name
Dim iCellColor As Integer
iCellColor = ActiveSheet.Shapes(Application.Caller) _
..Fill.ForeColor.SchemeColor - 7
For Each nName In ActiveWorkbook.Names
If nName.Name = "New_Color" Then
Let nName.Value = iCellColor
4. Right click one of the colored shapes>select "Assign Macro" from the
popup menu>Select "change_cell_color" from the list of macros. (It
could be the only one in the list, and it will probably have a sheet
name in front of its name)>click OK.
5. Repeat step 4 for each of the six colored shapes. They can't be all
done at once, they have to be assigned to the same macro separately.
6. Position the shapes near each other. If you ever have to scroll your
sheet you can prevent the shapes from moving out of view by positioning
them in the top few rows then select a column A cell that is just below
those top few rows, then Freeze Panes by going Window>Freeze Panes.
I hope this all makes sense.
I also hope this is the sort of function you were hoping to achieve.
If you have any trouble getting it to work feel free to email me then I
will return the email with a sample sheet attached.
hot key for background color posted by Fecozisk on Sat, 20 May 2006