enter number in cell when font color is changed

[Guest]

I would like a number to be automatically entered into a cell as the font
color of that cell is changed to a specific color. Any ideas? I am using
Excel 2003. I appreciate any help!

Suzi

 

[Gord Dibben]

You would need VBA code to do that

Unfortunately event code won't react to a change of color so you would need a
macro which you run manually.


Gord Dibben MS Excel MVP

 

[Guest]

unfortunately I am too new to Excel to know how to use macros - is how to do
this something you would share with me?

 

[Gord Dibben]

Can be done but do you want a choice of numbers depending upon the color?

Just one particular color and one specific number?

Just one cell or many cells?

First scenario..................

Sub test1()
Dim rng As Range
Set rng = ActiveSheet.Range("A1")
Select Case rng.Font.ColorIndex
Case Is = 3: Num = 1234 'red font
Case Is = 4: Num = 5678 'green
Case Is = 5: Num = 9876 'blue
Case Is = 6: Num = 4321 'yellow
Case Is = 7: Num = 8765 'pink
End Select
rng.Value = Num
End Sub

Second scenario...............

Sub test2()
With ActiveSheet.Range("A1")
If .Font.ColorIndex = 3 Then
.Value = 12345
End If
End With
End Sub

Since you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.

Another method would be a doubleclick or selectionchange event. If interested
post back with more details.


Gord