Button or key to conditionally change text foreground color?

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

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
>

I would use conditional formatting so that when you enter data in a cell it
will automatically lose the colour.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Kurt Swanson" <direct@reply.not.desired> wrote in message
news:m2u0akwt9y.fsf@kurt.swanson...
> 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

And I guees the code would help <g>

Sub SetColour()
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=" & _
ActiveCell.Address(False, False) & "="""""
.FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Kurt Swanson" <direct@reply.not.desired> wrote in message
news:m2u0akwt9y.fsf@kurt.swanson...
> 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

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> writes:
> I would use conditional formatting so that when you enter data in a cell it
> will automatically lose the colour.

Thanks, but I don't want to have the color change simply because it
gets a value. The idea is that the user would first start entering
values in black, then press a button, and then when empty fields are
filled in they are red. I.e. the button press signifies some crucial
point in time, and new data entered after that shows in a new color.
This so that the user know which fields were filled in before and
after the crucial point.
--
© 2005 Kurt Swanson AB

"Alex" <Alex@discussions.microsoft.com> 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.
--
© 2005 Kurt Swanson AB

Kurt Swanson <direct@reply.not.desired> writes:
> "Alex" <Alex@discussions.microsoft.com> 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

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 <direct@reply.not.desired> writes:
> > "Alex" <Alex@discussions.microsoft.com> 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
>