Button or key to conditionally change text foreground color?
 

Kurt

Add this code into the VBE (press ALT + F11) then <Insert<Module and cut
and paste the following code: into a module (most likely module 1):

Sub RedButton()
Dim rng As Range
Dim cl As Range

Set rng = Range("A1:I9")

For Each cl In rng
If IsEmpty(cl) Then
cl.Font.ColorIndex = 3
Else
cl.Font.ColorIndex = 0
End If
Next cl

End Sub

Now go back to worksheet and select <View<Toolbars<Forms and then click
the 'Button' icon and drag onto yor worksheet and 'assign' the macro
'RedButton'. Now right-click the button and <Edit Text and give the button a
name.

Each time you now press the button the empty cells in range A1:I9 will have
a red foreground.

Hope this helps


Alex


"Kurt Swanson" wrote:

I would like to find some quick method (some sort of visible button or
hotkey) for the user of a worksheet to change the text foreground
color for certain cells in a block of cells (A1:I9) to some specific color
if and only if the cell is blank. I.e. when the user hits this "red"
key/button, every empty cell in A1:I9 gets a new foreground text
color--red. Nothing would be immediately apparent, as these are empty
cells, but as soon as the user starts entering values into these
cells, the data would be shown in red... a "make all new data red"
button/key.

Is this possible? How?
--
© 2005 Kurt Swanson AB

Button or key to conditionally change text foreground color?
 

Kurt

Glad to hear that the code worked ok.

If you want to have multiple colours then the simplest way is to have
multiple buttons and just use the same macro but change the colorindex. You
can rename the VBA procedures accordingly e.g.

RedButton
BlueButton
GreenButton

etc.

There are other ways e.g. press button, get a dialog box which you pick
colour from, and then execute. But too complicated when you can just
reporduce the buttons.

Regards


Alex
"Kurt Swanson" wrote:

Kurt Swanson writes:
"Alex" writes:
Add this code into the VBE (press ALT + F11) then <Insert<Module and cut
and paste the following code: into a module (most likely module 1):

Sub RedButton()
Dim rng As Range
Dim cl As Range

Set rng = Range("A1:I9")

For Each cl In rng
If IsEmpty(cl) Then
cl.Font.ColorIndex = 3
Else
cl.Font.ColorIndex = 0
End If
Next cl

End Sub

Now go back to worksheet and select <View<Toolbars<Forms and then click
the 'Button' icon and drag onto yor worksheet and 'assign' the macro
'RedButton'. Now right-click the button and <Edit Text and give the button a
name.

Each time you now press the button the empty cells in range A1:I9 will have
a red foreground.

Alex, thanks--this is (almost) exactly what I want. I don't want to
forcibly change existing values to black, so I took out the two
"Else...ColorIndex = 0" lines.

However I was not able to make it work. I could define the
subroutine, but I was not able to create a button--all the buttons on
the Forms toolbar are greyed out. Secondly, if I simply manually run
the macro with alt-F8, I get an error: "unable to set the colorindex
property of the font class". I did some googling on this and found
there is some sort of bug in Excel XP about this. There seems to be a
work-around but I was not able to find it.

Ok, I was a little hasty. It seems I had very high security for macros
turned on, and certain cells were protected. I was able to unprotect,
add the button(s), then re-protect the specific cells. And thus all
is well. Strange thought, that it wouldn't let me add the buttons
when only unrelated cells were protected...

BTW, I actually need multiple buttons with different colors. Can I
make a macro with a parameter specified in the button, or just make
one macro per button that simply calls a sub with a specific
parameter? (The latter of which I've already done successfully...)
--
© 2005 Kurt Swanson AB