hot key for background color

[Guest]

imagine that im inserting data randomly in some cells. Suddenly i want that
every cell that I insert new data from now on gains red background.
in fact, i need hotkeys to five different colors!
it isnt difficult, is it?
i,ve tried recording a new macro, but it only change the actual background
colour, and I need it to make the new background color the standard for new
entries...
thanks!
Fernando,
São Paulo, Brazil

 

[mudraker]

Try this


On a normal module sheet you will code similar to this

Option Explicit

Public iColour As Integer

Sub Macro1()
' Keyboard Shortcut: Ctrl+Shift+R
iColour = 3 'red
End Sub

Sub Macro2()
' Keyboard Shortcut: Ctrl+Shift+Y
iColour = 6 ' yellow
End Sub

Sub Macro3()
' Keyboard Shortcut: Ctrl+Shift+B
iColour = 0 ' no colour
End Sub

On the worksheet module you will need

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value <> "" Then
With Target.Interior
..ColorIndex = iColour
..Pattern = xlSolid
End With
End If
End Sub

The Worksheet_Change macro will be trigged for every cell entry so it
will change the backgroud colour even when you edit an entry in a cell

 

[Ken Johnson]

Hi Fernando,

try this...

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"
button>click OK.
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]
End If
End Sub

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
End If
Next nName
End Sub

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.

Ken Johnson