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
0
direct (8)
2/27/2006 6:28:25 PM
excel.newusers 15348 articles. 2 followers. Follow

7 Replies
503 Views

Similar Articles

[PageSpeed] 37

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
> 
0
Alex4922 (316)
2/27/2006 7:07:26 PM
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


0
bob.phillips1 (6510)
2/27/2006 7:27:23 PM
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


0
bob.phillips1 (6510)
2/27/2006 7:30:07 PM
"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
0
direct (8)
2/27/2006 8:04:10 PM
"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
0
direct (8)
2/27/2006 8:09:06 PM
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
0
direct (8)
2/27/2006 9:29:38 PM
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
> 
0
Alex4922 (316)
2/27/2006 9:40:30 PM
Reply:

Similar Artilces:

Conditional Comment using Cell value as part of it
What I'm trying to do is the following: if cell(a1) is different from 0(zero) then, cell(b1) should have a comment that say, the value of cell(c1) is the payment, next line the value of cell(a1) was refund, next line Total for today = cell(c1)-cell(a1) Example a1 = $10.00 c1 = $30.00 B1 (comment): $30.00 is the payment (c1) $10.00 was refund (a1) Total for today: $20.00 (c1-a1) can anyone help me on that? thanks in advance ...

how can I fix the size of frame? once I fixed ,the user cannot changed its size
handle WM_GETMINMAXINFO see http://www.codeguru.com/forum/showthread.php?t=318933 "msnews.microsoft.com" <on_lixing@163.com> д���ʼ� news:OgxZDM45FHA.472@TK2MSFTNGP15.phx.gbl... > > Handle CFrameWnd::OnGetMinMaxInfo and specify whatever size you want for your frame to be. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "msnews.microsoft.com" <on_lixing@163.com> wrote in message news:OgxZDM45FHA.472@TK2MSFTNGP15.phx.gbl... > > ...

How to copy text from a picture/image & make it available for edi.
I am trying to copy a text from a picture/image and make it available in excel/word/powerpoint as normal text and not as an image/picture. ...

navigation buttons missing
In my subform I have navigation buttons set to yes but when the form is displayed the navigation buttons are not there. any ideas? -- deb "deb" <deb@discussions.microsoft.com> wrote in message news:BC539484-6057-49B6-BCFD-D887968CCD5C@microsoft.com... > In my subform I have navigation buttons set to yes but when the form is > displayed the navigation buttons are not there. > > any ideas? > -- > deb Sounds like the sub form control is a bit to short. See what happens if you allow scrollbars on the control. -- Bill Mosca, Microso...

Change font size based on value of a cell
If the value of A1>0, I need the font size in a merged cell to change from the default 10 to 16. It needs to return to the default size when A1 returns to a value of 0. Can someone help with this? Thanks. Michael Here's a little macro that will do it....... Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Range("a1").Value = 0 Then Range("B1:E3").Font.Size = 10 Else Range("B1:E3").Font.Size = 16 End If Range("B1").Select End Sub Vaya con Dios, Chuck, CABGx3 "MichaelRLanier@gmail.com" wrote: > ...

Chart Value Changes in VBA
When you have say a bar chart and drag the bar downwards this will change the value in the reference source cell. Is there a way through VBA to identify the source and the cell changed by when dragging the bar. That is, when you drag the bar in the chart let me know the cell that ahs changed! Any help would be appreciated. Thanks in advance. Howard John, If your objective is to prevent the cell changes via the chart, you can protect the worksheet (Tools - Protection). The cells should be locked (Format - Cells - Protection). Or you can use the Chart_SeriesChange event macro for the cha...

Changing password problem
my users are not able to change their outlook passwords from their computers. I am running Exchange 2000, the clients are running Outlook 2000 and XP, on Windows NT/2000/and XP. Any ideas Eric Are your clients use exchange version of outlook or POP version of outlook? "Eric Graham" <egraham22@yahoo.com> wrote in message news:#Nl0C$$nEHA.800@TK2MSFTNGP14.phx.gbl... > my users are not able to change their outlook passwords from their > computers. I am running Exchange 2000, the clients are running Outlook 2000 > and XP, on Windows NT/2000/and XP. Any ideas >...

Outlook Error after URL change
We have already configured our internal DNS records to support crm.domain.com to point to the proper machine, and created the host headers on the web server. How do we get CRM to switch from "server1" to crm.domain.com? In a nutshell, we renamed the server from an internal name to a FQDN. Internally the CRM server still believes it is on http://server1 We are trying to access it now on http;//crm.domain.com. Our outlook worked properly before we made this change. After the change, only the web works and the outlook after a uninstall and reinstall, switching the server name whil...

text box #7
I have created a text box, but when I type in something, and hit enter, I get the error message "REFERENCE NOT VALID". Please help. Thank you We require some more details. Where have you created the textbox, in the sheet or a userform. Are you using the control from the Control Toolbox menu. Are there any macros you are using. Mangesh "Marlis" <Marlis@discussions.microsoft.com> wrote in message news:6C6C6F42-E6CB-4DEB-BB46-C8F923A343C0@microsoft.com... > I have created a text box, but when I type in something, and hit enter, I get > the error message "...

Change base language
Hi I need to change the base language of CRM 4.0 I think I have to uninstall CRM and delete the CRM databases and then install again in the other language, it's not a big problem because there is no data in CRM but what about the registration would there be any problems registering the same server again ? /Jack There will be no problem in registeration .Never seen it . ------ Aamir Blog = http://mscrmsupport.wordpress.com/ No it all went well, uninstalled and deleted the databases and a new registration with same information and we are up running the right base language :) /J...

OneNote IE8 send to button
I am running IE8 and do not have a OneNote button in either the toolbar of under the tools menu. rkalb wrote: >I am running IE8 and do not have a OneNote button in either the > toolbar of under the tools menu. Check the add-ons in IE (Tools | Add-ons ....) if "Send to OneNote" is activated. Rainald ...

Change color for each serie?
Hi all, Is it possible to to change the color in a chart where 3 dataseries is represented? Eg. one color representing one region I have tried to create 3 series in the chart unsuccesfully. It seems that excel dosent allow it although I only have about +60 data in each serie... The data is also sorted (by size and not by region), so it is not possible just to select the data and assign them a specific value representing one color.. Hope that my question is clear. :) Christian ...

Varying colors on a line chart with multiple series
In Excel 2003, is it possible to change the color of part of a line on a chart with multiple data series? I can do it on a single series chart. Hi, Yes, you would need to select the segment of the line and then format it. So select the series pause and then select the segment apply formatting Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "LKP" <LKP@discussions.microsoft.com> wrote in message news:CB5CF5B8-5E3C-4288-A7B1-63958DB0C688@microsoft.com... > In Excel 2003, is it possible to change the color of part of a line on a > chart with ...

How do I retrieve changes to an unsaved excel document
I closed a excel document without saving changes. Is there a way to retrieve my old worksheet which would have the changes I made. I'm using excel 2000 which is part of my Microsoft Office 2000 program. No it's not possible -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Jim" <Jim@discussions.microsoft.com> wrote in message news:4FABAC1F-A402-4C87-BCDA-1124E072D9D3@microsoft.com... >I closed a excel document without saving changes. Is there a way to >retrieve > my ...

Equations and Text in same Cell
I am using Excel to write a program. I would like to incorporate text within the same cell as the result of an equation but the I cannot control the number of decimal places that the output displays. I've tried using the Number tab in the Format Cell feature but nothing changes. Is there a way to control the number of decimal places when you are combining text with the results of an equation? yes. you could incorperate the round() funciton. post your formula regards FSt1 "Eric" wrote: > I am using Excel to write a program. I would like to incorporate...

Charting with concatenated numbers and text
I am scatter charting from columns of data (text and numbers) that I concatanate into a long string so that I can use the validate drop down list feature in Excel, which only supports one column. I use Mid, Left and Right functions to then pick the relevant charting data from the string. Unfortunately although the numbers look good, Excel does not treat them the same way that it does with directly entered numbers - and my charts do not display accurately. Any ideas to to fix this problem or suggest a modified workflow. I am not an advanced user but would like to use the drop down li...

color list view
hi can i use thread to change the color of the list view with the OnNMCustomdraw and how can i do it ùthanks All this should be done on the same thread where the control was created. Why would you want a separate thread anyway? -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "mido1971" <jan_mido_2@hotmail.com> wrote in message news:439D8B44-5A8B-4FC8-99E4-D7A62E510E3B@microsoft.com... > hi > can i use thread to change the color of the list view with the > OnNMCustomdraw and how can i do it �thanks i need an another thread because i have in the list view lo...

Change Chart Size
I was wondering if anyone knows how I can change the sizes of charts in excel. But i dont want to just resize by dragging the corners, i want to lock the aspect ratio, and change the width to 3.5 inches. So basically, i want to change the width of each chart to 3.5 inches exactly. Another problem I am having is when I copy and paste a chart from excel into powerpoint, all the changes to the size and text I made get changed so its not the copy I wanted. Is there any way to change this. 1) Resize using aspect ratio only after the chart is sucessfully in Powerpoint. 2) You get charts...

Formatted Text in Autocorrect, Word 2008 for Mac
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I want to set up an autocorrect entry where I replace something with =formatted= as opposed to =plain= text. Specifically, I want to replace "qqq" with "QQQQQ" formatted in 14 pt Britannic Bold. However, I cnanot change the signal button from "plain' to "formatted" text. is there a way to do this? Type the word you want to use to replace "qqq" and format it inside your document. Select it, then click on Tools>AutoCorrect. You can now click the "formatted text&...

worksheet_calculate event changes worksheets, but shouldn't
Hi, The code below is in the Sheet1 (Budget) Module: The user is entering data into the "Budget" worksheet. When this fires, it should hide some rows on the Categories worksheet. It does work ok, however, everytime a user enters a new figure on the Budget worksheet, it fires and momentarily flashes over to the Categories worksheet. It is quite distractive, especially when one has hundreds of figures to enter. I set a trap to stop it on the top line of this code - the Private Sub Worksheet_Calculate() line. Then, when I enter a new number on the Budget page, it fi...

transpose music key
An odd request I know, but has anyone written code or functions t transpose music from one key to another?. As this is basically mathematical process it should be something Excel can do, but my musi skills are too rusty to manage -- Message posted from http://www.ExcelForum.com Both your input and your output would have to be in nonstandard notation; so why would you want to do it in Excel instead of a music notation package, where transposing would be basic functionality and you could use standard notation? Jerry Nicky < wrote: > An odd request I know, but has anyone written c...

Preventing "save changes" dialog box???
My spreadsheet data is stored in the range A1:J20. Therefore, I do not care if a user plays with or modifies cells that are outside of this range. Can I somehow program Excel to prevent the "Do you want to save changes?" dialog box if a user changes cells that are out of range and then s/he closes the workbook?? I basically only want the "save changes" dialog box to be prompted when cells within the A1:J20 range are modified. Thank you! Right click the worksheet tab that your concerned with and paste this in: Code: -------------------- Priv...

How do you make an text box opaque?
I want to use excel spreadsheet and blank out certain parts of a already make form i believe it is called opaque. What do mean by "blank out"? What is it you want to do? HTH Otto "mcnally" <mcnally@discussions.microsoft.com> wrote in message news:55729CA5-2247-4433-B73F-0DC151E851A2@microsoft.com... >I want to use excel spreadsheet and blank out certain parts of a already >make > form > i believe it is called opaque. I have a form that I want to put a text box on it and type something over whatever under it. it keeps typing whatever is under i...

my forward button is not available to pick what do i do?
my forward button is not available to pick what do i do? I cannot forward any of my emails please help Does Ctrl+F work? Did you try resetting the toolbar or looking in the overflow? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging Outlo...

Changing Entity Name
We need to change the wording from "Account" to "Customer". can we do that? On Wed, 27 Jul 2005 02:15:03 -0700, Shawki <Shawki@discussions.microsoft.com> wrote: >We need to change the wording from "Account" to "Customer". can we do that? Not possible in CRM I'm afraid! You will be able to do this in the next version of CRM, 3.0. -- Matt Wittemann http://icu-mscrm.blogspot.com "Shawki" wrote: > We need to change the wording from "Account" to "Customer". can we do that? This is possible. Unsupported t...