Button or key to conditionally change text foreground color?

  • Thread starter Thread starter Kurt Swanson
  • Start date Start date
K

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?
 
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
 
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)
 
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)
 
Bob Phillips said:
I would use conditional formatting so that when you enter data in a cell it
will automatically lose the colour.
Click to expand...

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.
 
Alex said:
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):
Click to expand...
Sub RedButton()
Dim rng As Range
Dim cl As Range
Click to expand...
Set rng = Range("A1:I9")
Click to expand...
For Each cl In rng
If IsEmpty(cl) Then
cl.Font.ColorIndex = 3
Else
cl.Font.ColorIndex = 0
End If
Next cl
Click to expand...
End Sub
Click to expand...
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.
Click to expand...
Each time you now press the button the empty cells in range A1:I9 will have
a red foreground.
Click to expand...

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.
 
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.
Click to expand...
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.
Click to expand...

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...)
 
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
 
Back
Top