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

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


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=544035

Hi Fernando,

try this...

1. Insert a name (New_Color) by going InsertNameDefine 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"
buttonclick 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 codesRight click the sheet tab select "View
Code" from the popupPaste the code into the sheet's code modulePress
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 shapesselect "Assign Macro" from the
popup menuSelect "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 WindowFreeze 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