Conditional formatting of text

Greetings, all -

I am creating a simple spreadsheet template to be distributed to users 
for data entry purposes. The first column contains the names of the 
desired data elements. The second column contains the definition for 
each data element. The definitions are formatted in red text. I intend 
for the user to remove the definition and replace it with the proper 
information for each element. For example:

Data Element          Definition
1.0 LastName          The last name of the person to whom questions
                       about the sample should be directed.

The user would replace the definition with, say, "Smith" using the usual 
method for overwriting data in a cell (i.e. click'n'type). Once the 
definition has been overwritten and the user hits Enter, I would like 
for the font color to change from red to black. Here's the rub...this 
has to be done without macros or VB of any kind. I'm hoping to use 
Conditional Formatting combined with some native formula that I have yet 
to identify; something like the "IS" functions, perhaps? Alas, if only 
there was such a thing as ISNEW()...

Ideas?

tia,

LeAnne
0
nospam2791 (369)
3/2/2005 3:56:10 PM
excel.newusers 15348 articles. 2 followers. Follow

7 Replies
363 Views

Similar Articles

[PageSpeed] 51

You would need a macro for this, no ISNEW function and conditional formats
only work with built in functions

-- 

Regards,

Peo Sjoblom


"LeAnne" <nospam@nospam.net> wrote in message
news:O$WRXD0HFHA.3040@TK2MSFTNGP10.phx.gbl...
> Greetings, all -
>
> I am creating a simple spreadsheet template to be distributed to users
> for data entry purposes. The first column contains the names of the
> desired data elements. The second column contains the definition for
> each data element. The definitions are formatted in red text. I intend
> for the user to remove the definition and replace it with the proper
> information for each element. For example:
>
> Data Element          Definition
> 1.0 LastName          The last name of the person to whom questions
>                        about the sample should be directed.
>
> The user would replace the definition with, say, "Smith" using the usual
> method for overwriting data in a cell (i.e. click'n'type). Once the
> definition has been overwritten and the user hits Enter, I would like
> for the font color to change from red to black. Here's the rub...this
> has to be done without macros or VB of any kind. I'm hoping to use
> Conditional Formatting combined with some native formula that I have yet
> to identify; something like the "IS" functions, perhaps? Alas, if only
> there was such a thing as ISNEW()...
>
> Ideas?
>
> tia,
>
> LeAnne


0
terre081 (3244)
3/2/2005 4:22:19 PM
Hi,

Could you not use an additional column that is a copy of the Definition, 
call it masterdefinition. This column can be hidden.
Then when the user changes the definition you can use a CF to test their 
definition against masterdefinition.

Cheers
Andy

LeAnne wrote:
> Greetings, all -
> 
> I am creating a simple spreadsheet template to be distributed to users 
> for data entry purposes. The first column contains the names of the 
> desired data elements. The second column contains the definition for 
> each data element. The definitions are formatted in red text. I intend 
> for the user to remove the definition and replace it with the proper 
> information for each element. For example:
> 
> Data Element          Definition
> 1.0 LastName          The last name of the person to whom questions
>                       about the sample should be directed.
> 
> The user would replace the definition with, say, "Smith" using the usual 
> method for overwriting data in a cell (i.e. click'n'type). Once the 
> definition has been overwritten and the user hits Enter, I would like 
> for the font color to change from red to black. Here's the rub...this 
> has to be done without macros or VB of any kind. I'm hoping to use 
> Conditional Formatting combined with some native formula that I have yet 
> to identify; something like the "IS" functions, perhaps? Alas, if only 
> there was such a thing as ISNEW()...
> 
> Ideas?
> 
> tia,
> 
> LeAnne

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
3/2/2005 5:00:35 PM
(OT: Do you folx top- or bottom-post here? Guess I'll bottom-post to be 
on the safe side)

Peo Sjoblom wrote:

> You would need a macro for this, no ISNEW function and conditional formats
> only work with built in functions
> 

Peo, thx for replying. As stated upthread, unfortunately I cannot use 
code to accomplish this. I guess what I'm trying to do is mimic the 
AfterUpdate event. I'm not wedded to Conditional Formatting, that just 
seemed like the most likely option. But any other approaches would be 
welcome.

thx again,

LeAnne
0
nospam2791 (369)
3/3/2005 5:44:34 PM
Andy Pope wrote:

> Hi,
> 
> Could you not use an additional column that is a copy of the Definition, 
> call it masterdefinition. This column can be hidden.
> Then when the user changes the definition you can use a CF to test their 
> definition against masterdefinition.
> 
> Cheers
> Andy
> 

Andy, not sure at all that I follow you. How could "Smith" be tested 
against "The last name of the person to whom questions about the sample 
should be directed"? Other than cofirming that the replaced defitintion 
ISTEXT or something.

Puzzled,

L.
0
nospam2791 (369)
3/3/2005 6:00:31 PM
Hi LeAnne,

Hopefully this will make things clearer.
http://www.andypope.info/ngs/ng35.htm

If I have mis-understood let me know.

Cheers
Andy

LeAnne wrote:
> Andy Pope wrote:
> 
>> Hi,
>>
>> Could you not use an additional column that is a copy of the 
>> Definition, call it masterdefinition. This column can be hidden.
>> Then when the user changes the definition you can use a CF to test 
>> their definition against masterdefinition.
>>
>> Cheers
>> Andy
>>
> 
> Andy, not sure at all that I follow you. How could "Smith" be tested 
> against "The last name of the person to whom questions about the sample 
> should be directed"? Other than cofirming that the replaced defitintion 
> ISTEXT or something.
> 
> Puzzled,
> 
> L.

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
3/4/2005 1:01:18 PM
Andy,

Oh...my...God.

That is absolutely brilliant. BRILLIANT, I say!

By the power vested in me, I hereby dub thee DA MAN.

Thankyouthankyouthankyou!!!!!!!

Cheers,

LeAnne


Andy Pope wrote:
> Hi LeAnne,
> 
> Hopefully this will make things clearer.
> http://www.andypope.info/ngs/ng35.htm
> 
> If I have mis-understood let me know.
> 
> Cheers
> Andy
> 
> LeAnne wrote:
> 
>> Andy Pope wrote:
>>
>>> Hi,
>>>
>>> Could you not use an additional column that is a copy of the 
>>> Definition, call it masterdefinition. This column can be hidden.
>>> Then when the user changes the definition you can use a CF to test 
>>> their definition against masterdefinition.
>>>
>>> Cheers
>>> Andy
>>>
>>
>> Andy, not sure at all that I follow you. How could "Smith" be tested 
>> against "The last name of the person to whom questions about the 
>> sample should be directed"? Other than cofirming that the replaced 
>> defitintion ISTEXT or something.
>>
>> Puzzled,
>>
>> L.
> 
> 
0
nospam2791 (369)
3/4/2005 2:27:54 PM
I will take that as a "Yes it made things clearer" :)

LeAnne wrote:
> Andy,
> 
> Oh...my...God.
> 
> That is absolutely brilliant. BRILLIANT, I say!
> 
> By the power vested in me, I hereby dub thee DA MAN.
> 
> Thankyouthankyouthankyou!!!!!!!
> 
> Cheers,
> 
> LeAnne
> 
> 
> Andy Pope wrote:
> 
>> Hi LeAnne,
>>
>> Hopefully this will make things clearer.
>> http://www.andypope.info/ngs/ng35.htm
>>
>> If I have mis-understood let me know.
>>
>> Cheers
>> Andy
>>
>> LeAnne wrote:
>>
>>> Andy Pope wrote:
>>>
>>>> Hi,
>>>>
>>>> Could you not use an additional column that is a copy of the 
>>>> Definition, call it masterdefinition. This column can be hidden.
>>>> Then when the user changes the definition you can use a CF to test 
>>>> their definition against masterdefinition.
>>>>
>>>> Cheers
>>>> Andy
>>>>
>>>
>>> Andy, not sure at all that I follow you. How could "Smith" be tested 
>>> against "The last name of the person to whom questions about the 
>>> sample should be directed"? Other than cofirming that the replaced 
>>> defitintion ISTEXT or something.
>>>
>>> Puzzled,
>>>
>>> L.
>>
>>
>>

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
3/4/2005 2:34:40 PM
Reply:

Similar Artilces:

Conditional Formatting across different worksheets 04-28-10
Hi everyone, I am reposting my message, as the previous one was not clear. I think that I have to go to VBA, as it was suggested. Does anyone have the code? The problem is as below: I have two sheets, one that I use as a control (i.e. a back up) that I will call Sheet 2, while Sheet 1 is a duplicate of Sheet 2 and is sent out to different people for quarterly updates. The Excel file that I send out for updates has both Sheet1 and Sheet2, but Sheet2 is hidden. What I want to do is make the cells in Sheet 1 automatically change color if they are edited and no longer match Shee...

Formatting #3
Is there any rhyme or reason to why Outlook changes my formatting (HTML, rich text, plain text) to messages that are forwarded? For example, someone forwarded my a message that was HTML on her computer, however I recieved it as plain text. We are both using Outlook 2000. Maybe she had your address set for plain text only. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Jamie Hart" <jhart@emaple.net> wrote in message news:ucMf5padEHA.176...

query to extract the portion of text of fields in a Table
All: I have a table have several fields that I would like to modify from its original text content to only retain its first 25 characters. How to do it in a query such that it could overwrite the fields of that table? Thanks. USE an update query and the Left function. UPDATE YourTable SET FieldA = LEFT([FieldA],25) , FieldB = Left([FieldB],25) This is a PERMANENT change. In the query grid -- Select the fields you want to modify -- Select Query: Update from the menu -- in UPDATE TO under each field you want to change type Left([Name of field],25) -- Select Query: Run from the menu. WARN...

should be possible to add more conditions to conditional formatti.
In todays Excel it is only possible to set 3 seperate conditions for formatting cells. I'd like to have some added. Often I just need to set 4 or 5 conditions to cells. For example: I made a spreadsheet for registrating absence at work. There are many reasons why someone is not at work. You can simply give these reasons a colour, but three is not enough. Hi Jan, I agree that you should be able to add many more conditions. Check this link to see a VBA solution that will allow you to add as many as you like. http://www.mrexcel.com/board2/viewtopic.php?t=119185&highlight= Cheer...

Product Codes Numbers
I am using Excel to edit the Sage Stock Records for my business. So export from sage as a CSV, make changes in Excel and import back int Sage. Generally it is working fine. But I have a problem with Product Codes The format of my Product Code is 6 numbers and so that I can us Excel's mathematical functions I am using a custom '000000' format. But Sage needs the Product Code column in a text format. So prior t import (ie having calculated the Product Code numbers) I change th format of the Excel column to text. The problem is that, in doing this I lose any zeros that may be th...

Conditionally summing data from two columns depending on their value.
I have two columns of data: mileage in A2:A20 and number of passengers in B2:B20. Each row corresponds to a particular trip. I wish to sum the number of passenger-miles for trips that have traveled within a distance range of my discretion (the lower and upper bounds are specified in A22:A23). How would I do this? Thanks! One way... =SUMIF(A2:A20,">="&A22,B2:B20)-SUMIF(A2:A20,">"&A23,B2:B20) -- Biff Microsoft Excel MVP "Andy" <akk333@gmail.com> wrote in message news:76fe792a-9836-4f74-8056-14b2b7f632a5@h14g2000pri.goo...

How to copy Word text into an email
I did this all the time in the old Word. Now I can put things on the clipboard, but if I try to paste it in an email, nothing happens. What do I do??? Select the text, hold down the Ctrl key and press C, move to the other program and hold down the Ctrl key and press V. That works everywhere. It is not unique to Word. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Moira J" <Moira J@discussions.microsoft.com...

Copying Conditional Formatting ONLY
How do you paste the Conditional Formatting properties of one cell into another without also transferring other properties (number format, font, etc.). Is this possible? Thanks "hmm" <hmm@hmm.com> wrote in message news:3FCAFC3A.1BEF6313@hmm.com... > How do you paste the Conditional Formatting properties of one cell into > another without also transferring other properties (number format, font, > etc.). Is this possible? > > Thanks > Assuming that the cell (or range) to which you want to apply the conditional format isn't already conditionally formatted...

change date-format
In a worksheet I got 3000 cells, all with dates. The format of these dates looks like mm-dd-yy; this is an american format; I want to have changed this to dd-mm-yy. How can I solve this probem? Y. Format-->Cells. On the Number tab, choose Date. Choose from the options shown on the right of the dialog. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Yippie" <Yippie@yahoo.com> wrote in message news:dagqr0$j5r$1@news5.zwoll1.ov.home.nl... > In a worksheet I got 3000 cells, all with dates. > The format of these dates looks like mm-dd-yy; ...

Notepad automatic text formatting
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Everytime I highlight a word or phrase and 'bold', 'underline' or 'italicize' it, the next bullet point in my outline list automatically starts with that format. I have to manually change it back to 'normal' font attributes every single bullet point unless I start a new list. Is there a way around this? Thanks One thing that will help: Go to Tools> AutoCorrect � AutoFormat As You Type then remove the last check pertaining to "Format beginning of list...". It would be even more beneficial to...

Formatting Text Cells
I have cells that have variable length text in them. I want to forma the cell so the total length of the cell is 35 characters. In othe words, after the text, I would like spaces following the text, so th total number of characters will be 35. Thanks for your help -- JohnnyCa ----------------------------------------------------------------------- JohnnyCat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1658 View this thread: http://www.excelforum.com/showthread.php?threadid=31978 Hi you could use the following in a helper column: =A1 & REPT(" &q...

Converting text format of time/date into Excel time/date for subtr
Hi, I need some help on date. I have the date and time in text format. 13/01/2010 10:34 AM 13/01/2010 1:56 PM I need a formula to convert them to excel date/time format so that I can do subtraction between these dates. Thank you very much, YY =DATEVALUE(LEFT(A1,FIND(" ",A1)-1))+TIMEVALUE(MID(A1,FIND(" ",A1)+1,256)) -- Gary''s Student - gsnu201001 "YY san." wrote: > Hi, > I need some help on date. I have the date and time in text format. > 13/01/2010 10:34 AM > 13/01/2010 1:56 PM > > I need a formula to con...

string format question
Hi, I want to make a dynamically string format padding on a given string but I get ABORT when doing it: string aTest = string.Format("--{0,10}--", "test"); // this is OK string aTest2 = string.Format("--{0,-10}--", "test"); // this is OK too int aNum = 10; string aTest3 = string.Format("--{0,-aNum}--", "test"); // trying to make the padding value dynamically -> ABORT can someone please help ? thanks, Yoav. Yoavo wrote: > string aTest2 = st...

Entering text that start with Zero ?
How do I enter text that starts with a zero and have the cell show the zero. Example : 0256 0891 0188 etc Thank You. Start with a leading single quote, which won't show. Bob Umlas Excel MVP "toyota58" <toyota58@lycos.com> wrote in message news:1124474041.749743.316410@g49g2000cwa.googlegroups.com... > How do I enter text that starts with a zero and have the cell show the > zero. > > Example : > > > 0256 > 0891 > 0188 > etc > > > > Thank You. > "toyota58" <toyota58@lycos.com> wrote in news:1124474041.7...

Free text Jokes,Money,games,toutrial,calls,TV
Dear Sir Join our site and enjoy with all service and Offers we present for you and wait more from us, if any subject admired you, write comment. http://mony4free.blogspot.com FREE INTERNATIONAL INTERNET PHONE CALLS AND SMS FREE INTERNATIONAL CALLS USA, CANADA: FREE TEXT JOKES FREE MONEY FROM GOOGLE ADSENSE EARN MORE THAN YOU COULD IMAGINE WORK-AT-HOME OPPORTUNITIES LEARN HOW TO MAKE FREE CALLS WORLDWIDE. FREE GOVERNMENT GRANTS WIN MONEY FROM AGLOCO FREE INTERNATIONAL TV , SATELLITE CHANNELS FREE PLAYING GAMES ONLINE MAKE MONEY DOING ONLINE PAID SURVEYS ONLIN...

formating
I have a music data base and I was able to import it into excel. In one cell I have the artist listed by last name - comma and first name. Is there a way I can format the cell so I have the first name (space) last name - comma? I have twenty thousand name in the data base. ...

CRichEditCtrl::Create and RTF format
Hi, I have created a CRichEditControl on one of the dialogs programmatically - pRichCtrl->Create( WS_CHILD|WS_VISIBLE|ES_READONLY|ES_MULTILINE|ES_OEMCONVERT , rect , this , uiCtrlId) Now I want to be able to use rtf format to format the text but even simplest rtf format wont work - strTxt = "{\\rtf1\\ansi This is in \\b bold\\b0.}"; pRichCtrl->SetWindowText( strTxt ) ; I am compliing with UNICODE defined. Wherever I have CRichEditControl on the dialog template, this kind of formattin works fine. Can someone help ? Thanks in advance, Neel. I forgot to mention - After...

Saving the report as a text file
Hi all, Is it possible to save a report as a text file or RTF file? I am new at this, so I may have missed it. Dwight There should be a button on your toolbar which will let you export your report to Word. This creates an RTF file. HTH "Dwight" <edshubert@comcast.net> wrote in message news:aOKdnaxqvvVClqDbnZ2dnUVZ_s-rnZ2d@comcast.com... > Hi all, > > Is it possible to save a report as a text file or RTF file? I am new at > this, so I may have missed it. > > Dwight > Thank you. I missed it. "MacDermott" <macdermott@NoSpam.com&g...

Can Pivot display text as data values
Hi! I'm trying to create a "dynamic phonebook" using Pivot. I.e. I can select from Page e.g. City and department, in Row Field there's listed Role and Name and In Data Items phone number and email-address. It basicly looks great, but it seems its impossible to display text values of the data. Is it possible to display text in data items? Example Page: City:Capitol City Dep.:IT-Department Rows: Role: Field Engineer Name: John Doe Data: Phone: 01 23456 Email: john.doe@mail.com I know Pivot wasn't meant for this purpose, but it would be a good tool for tha...

Conditional formating in graphs
I would like to know if you can apply some conditional formatting in a graph data label. An example is if a variable X is below a determined value Z then in the graph the data label for variable X is in red, but if X >= Z then the data label on the chart should be in black. Is this posible? I would appreciate any help you can offer me. This page has a couple examples showing how to format the individual points: http://www.geocities.com/jonpeltier/Excel/Charts/format.html What you might do is set up separate series, format the points the same, but format the labels differently. ...

Exchange 2003 Full Text Indexing via a web based search page?
I am attempting to locate a web based search page that I can use with an Exchange 2003 full text index. Has anyone come across something like this? Thank you in advance. Sam OWA has a "Find" page that will leverage a Full-Text Index if one exists. Is that what you are looking for? --Chris ============================================================================ ==== This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm. Please d...

Unable to Display the "Format Cells" Dialog Box
I have a worksheet which is NOT protected. With some cells, when I try to display formatting options, the "Format Cells" dialog box does not appear. I've tried every method I can think of: right-click, then select "Format Cells" from the menu, choosing Format, Cells keyboard short-cut - Ctrl - 1 All I get is my cell pointer in the cell I want to format, but no dialog box. I've seen this happen in other spreadsheets I've worked on, usually with files that have been in existence for a while, used by various users, could have had worksheets inserted/cop...

Text Wrap does not work in 2003 Publication PRINT Publication Docu
The text wrap does not work in a publication document. I am trying to wrap text around a graphic chart. This should be no big deal. Are you sending the text box to the back? Do you have a border around the perimeter of your publication? If you do, send it to the back after you send the text box back. -- Mary Sauer http://msauer.mvps.org/ "Dana C." <Dana C.@discussions.microsoft.com> wrote in message news:62E9DE45-5208-456F-8D0F-0051E9FC8E53@microsoft.com... > The text wrap does not work in a publication document. I am trying to wrap > text around a graphic chart...

Dae format
how can we convert the date format, suppose I am entering the date as 09/09/2009 using an input box, the macro should take the date as 9/9/2009. Please help A date is a date is a date. Those two are the very same, september 9th this year. HTH. Best wishes Harald "SR" <SR@discussions.microsoft.com> wrote in message news:A21EC898-7F46-48E2-A313-35BC938126CB@microsoft.com... > how can we convert the date format, suppose I am entering the date as > 09/09/2009 using an input box, the macro should take the date as 9/9/2009. > > Please help on my ...

Excel File Format SUDDENLY Not Valid-What happened?????
I tried to open an Excel Workbook that I last used a couple of weeks ago and got an error message the the file format was not valid; I''ve never seen thise before and as I say, it was fine a couple of weeks ago. What happened? check the file extension. It should be .xls it might have been accidently changed -- Anna ----------------------------------------------------------------------- AnnaV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=961 View this thread: http://www.excelforum.com/showthread.php?threadid=49697 ...