conditional formating with text

I have two columns of data (A:B), and want to highlight values in Col B in 
coloured text, where the adjacent cell in Col A contains data entered in 
brackets. The data in column A is a mixture of numbers and text and formatted 
as 'General' so that a number in brackets does not appear as a negative 
value. I've used CF before but can't seem to enter a formula that works.

Any ideas?



0
Utf
11/16/2009 4:45:04 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
588 Views

Similar Articles

[PageSpeed] 52

If there cannot be any other parenthesizes then you can use

In the CF use formula is and


=ISNUMBER(FIND("(",A1))


-- 


Regards,


Peo Sjoblom


"snakey" <snakey@discussions.microsoft.com> wrote in message 
news:81537FA4-6134-4A0E-B5D2-6A8DD38308D5@microsoft.com...
>I have two columns of data (A:B), and want to highlight values in Col B in
> coloured text, where the adjacent cell in Col A contains data entered in
> brackets. The data in column A is a mixture of numbers and text and 
> formatted
> as 'General' so that a number in brackets does not appear as a negative
> value. I've used CF before but can't seem to enter a formula that works.
>
> Any ideas?
>
>
> 


0
Peo
11/16/2009 5:00:08 PM
1. Select the cell/Range (say b1:b10). Please note that the ROW reference 1 
mentioned in the formula is the active ROW in the selection. Active cell will 
have a white background even after selection

2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula

=AND(LEFT(A1,1)="(",RIGHT(A1,1)=")")

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK


If this post helps click Yes
---------------
Jacob Skaria


"snakey" wrote:

> I have two columns of data (A:B), and want to highlight values in Col B in 
> coloured text, where the adjacent cell in Col A contains data entered in 
> brackets. The data in column A is a mixture of numbers and text and formatted 
> as 'General' so that a number in brackets does not appear as a negative 
> value. I've used CF before but can't seem to enter a formula that works.
> 
> Any ideas?
> 
> 
> 
0
Utf
11/16/2009 5:27:02 PM
Many thanks Jacob - works a treat!

"Jacob Skaria" wrote:

> 1. Select the cell/Range (say b1:b10). Please note that the ROW reference 1 
> mentioned in the formula is the active ROW in the selection. Active cell will 
> have a white background even after selection
> 
> 2. From menu Format>Conditional Formatting>
> 3. For Condition1>Select 'Formula Is' and enter the below formula
> 
> =AND(LEFT(A1,1)="(",RIGHT(A1,1)=")")
> 
> 4. Click Format Button>Pattern and select your color (say Red)
> 5. Hit OK
> 
> 
> If this post helps click Yes
> ---------------
> Jacob Skaria
> 
> 
> "snakey" wrote:
> 
> > I have two columns of data (A:B), and want to highlight values in Col B in 
> > coloured text, where the adjacent cell in Col A contains data entered in 
> > brackets. The data in column A is a mixture of numbers and text and formatted 
> > as 'General' so that a number in brackets does not appear as a negative 
> > value. I've used CF before but can't seem to enter a formula that works.
> > 
> > Any ideas?
> > 
> > 
> > 
0
Utf
11/17/2009 10:53:01 AM
Thanks Peo, this also works, as does Jacobs suggestion - will now decide 
which one to use!

"Peo Sjoblom" wrote:

> If there cannot be any other parenthesizes then you can use
> 
> In the CF use formula is and
> 
> 
> =ISNUMBER(FIND("(",A1))
> 
> 
> -- 
> 
> 
> Regards,
> 
> 
> Peo Sjoblom
> 
> 
> "snakey" <snakey@discussions.microsoft.com> wrote in message 
> news:81537FA4-6134-4A0E-B5D2-6A8DD38308D5@microsoft.com...
> >I have two columns of data (A:B), and want to highlight values in Col B in
> > coloured text, where the adjacent cell in Col A contains data entered in
> > brackets. The data in column A is a mixture of numbers and text and 
> > formatted
> > as 'General' so that a number in brackets does not appear as a negative
> > value. I've used CF before but can't seem to enter a formula that works.
> >
> > Any ideas?
> >
> >
> > 
> 
> 
> .
> 
0
Utf
11/17/2009 10:54:04 AM
If there can't be more than one left parenthesis in the cell which I assume 
it is the case then I think mine is a tiny bit more robust since it is not 
sensitive to an occasional misplaced leading space.

-- 


Regards,


Peo Sjoblom


"snakey" <snakey@discussions.microsoft.com> wrote in message 
news:898687E8-C41F-4E1E-A1DE-C3013086CD1B@microsoft.com...
> Thanks Peo, this also works, as does Jacobs suggestion - will now decide
> which one to use!
>
> "Peo Sjoblom" wrote:
>
>> If there cannot be any other parenthesizes then you can use
>>
>> In the CF use formula is and
>>
>>
>> =ISNUMBER(FIND("(",A1))
>>
>>
>> -- 
>>
>>
>> Regards,
>>
>>
>> Peo Sjoblom
>>
>>
>> "snakey" <snakey@discussions.microsoft.com> wrote in message
>> news:81537FA4-6134-4A0E-B5D2-6A8DD38308D5@microsoft.com...
>> >I have two columns of data (A:B), and want to highlight values in Col B 
>> >in
>> > coloured text, where the adjacent cell in Col A contains data entered 
>> > in
>> > brackets. The data in column A is a mixture of numbers and text and
>> > formatted
>> > as 'General' so that a number in brackets does not appear as a negative
>> > value. I've used CF before but can't seem to enter a formula that 
>> > works.
>> >
>> > Any ideas?
>> >
>> >
>> >
>>
>>
>> .
>> 


0
Peo
11/17/2009 6:33:43 PM
Reply:

Similar Artilces:

help on cell formats
-------------------------------------------------------------------------------- thank you guys. but... may be i didnt explain clearly enough.... I have two columns... *H* and *I* ... in column *H *i'm entering a dollar amount.. $1,108.83.. once i hit enter, the *H* column should only show the dollar amount which is 1,108 and column *I* should only show the remaining cents which is 83 hope i explained better this time.. -- Joe@excel ------------------------------------------------------------------------ Joe@excel's Profile: http://www.excelforum.com/member.php?action=getin...

Automatically Format cell
Is there a way to set the formatting to automatically adjust cell size based on the text I type in? I know about using the Format-Autofit, but I'm looking for something that works while I type. Thanks, in advance, Amber Hi you'll have to use VBA for this. Put the following code in your worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub With Target If .Value <> "" Then .Columns.AutoFi...

Random text and numbers
I want to place a hidden field in a form which inserts data into an Access database backend. I am using ASP. Can anyone tell me how I can formulate the value of the hidden field so that what is entered in the database is a string of say 6 random letters and 2 numbers? Many thanks Andrew Turner I answered this yesterday. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L "Andrew Turner" <andrew.turne...

Reducing The Account Format
Is there a tool that allows you to reduce the number of segments you have allocated to you COA in a company and consolidate the accounts? I have a client that has 5 segments and wants to reduce it down to 3. They have posted transactions. Will PST or the product from Corporate Renaissance do this? I know they can increase it... How would you reduce it? Would you be removing separators or remoing two segments? For example: AA-BB-CC-DD-EE to AABB-CCDD-EE or AA-BB-CC- DD-EE to AA-BB-CC? >-----Original Message----- >Is there a tool that allows you to reduce the number of segment...

Wrap text does not work
Dear all, It seems that after changed the row height of the cell, tjhe wrap text does not work . How can I fix it? Thank you in advance. Hi Peter, Can you verify if it is wrap text that does not work or is it the automatic row height adjustment that does not work. Once you force the row height, Excel respects that and will not change the height automatically for that row(s). Here is a solution for a specific column by Bernie Dietrick. http://google.com/groups?threadm=%23yb%24bf4VEHA.3012%40tk2msftngp13.phx.gbl Here is a more comprehensive solution for merged cells by Jim Rech in 19...

adding email text to a new task in office 2003.
Hello everyone. In Office 2000 when you selected an email to be added to a task you could select text only and all the text would be copied into the new task, including the body text of the email. Now in Office 2003 I can only seem to include the To, From and the subject line fields in the new task. Is there a way to also include the body of the email without copy and past exercise. I hope there is an answer to this and gets a PA off my back Thanks. is the message in the same message store as you are saving the task to? Do you know if you are using an ANSI format (compatibl...

Applying numeric values in one cell based on text in another
Hello All, New and young technical writer in a bit of a stew due to my inexperience with MS Excel formulas. The following outlines what I am attempting to do, likely very basic to you gurus out there so please forgive my newness to this. In my worksheet, Column A contains a repeating series of drop down menus created using data validation. The choices int he drop downs are the names of courses offered by my training department. In Column B I would like the number of billable hours each course is worth automatically entered based on which course they entered in column A. I know there is a ...

How can I convert text to reference ? Ex: ISNUMBER(ADDRESS(6;10;2.
I'm trying to use above function to avoid doing it by coding. However ADDRESS function returns text and I cannot convert it to a reference, in order to use its result as input to other functions (like ISNUMBER or CELL). If I understand you correctly, try =INDIRECT(ADDRESS(G7,H7)) for example HTH, Bernd ...

email submission tool for converting xls to pdf or xls to plain text ascii
Where would there be an email submission tool ?... for converting .xls to pdf or converting .xls to plain text ascii something like the email submission tool at cern conversion service http://documents.cern.ch/Convert?emailGuide ...

Query Results in Text Box
Hello, I have an existing form with employee data. I have just finished building a query that is a summation of the hours that an employee has worked at the company. It is a totals query so it always returns one answer. What I would like to do is have the results of the query appear in the Employee Form (frmEmployees) that I have created. The query and form share the common field EmployeeID. Therefore on the query, I said the criteria for the EmployeeID was to be [Forms]! [frmEmployees]![EmployeeID]. When I double click the query and insert an Employee ID, it gives me the right number. ...

Restricting Cell Format
Is there a way to restrict a cell to a certain format, such that the user cannot input other formats other than the preset one? For example: preset the cell to numeric, user cannot input text or dates. Many thanks. Data Validation can do that to an extent. It can restrict to a number, no text, but dates are just numbers, so that is a problem. See www.contextures.com/xlDataVal01.html -- HTH RP (remove nothere from the email address if mailing direct) "zhj23" <zhj23@discussions.microsoft.com> wrote in message news:EEF1D4A6-59D9-4ED5-B81D-4A747269972A@microsoft.com... >...

How do I change the default "file origin" when opening a tab delimited text file?
Excel2002 /Win2K Hi When I save a spreadsheet to tab delimited text and re-open it (and this is something I do a *lot* to get rid of micro$oft formatting and stray HTML coding...) it always defaults the "file origin" to be "932:Japanese (Shift-JIS)" instead of "1252:Western European (Windows)"! How do I get my MS Excel to default to the correct thing?! ie I want it to default to: "1252:Western European (Windows)" and not "932:Japanese (Shift-JIS)" whenever I open a .txt file ! Ship Shiperton Henethe ...

Stop Tile format printing in 8.5 x 11- unsolved
Hi, In published 2007 I am trying to print a standard letter size A3. When I go to print it tiles it into 4. I have tried changing the printer settings and changing everything previously suggested. Does anyone know how to solve this problem? Thank you A3 is not a standard letter size. That said, can your printer handle A3? Some printers cannot and this could be the reason you are getting tiling. When you are finished designing your publication, File, print, it is this screen you will have to setup again for your A3 stock. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msau...

Copy and paste formatting with keyboard in Excel 2007?
Is there any way to copy and paste formatting from one cell to another in Excel (2007 or otherwise)? I'm looking for an analog of ctrl-shift- c / ctrl-shift-v from Word. ajkessel <ajkes...@gmail.com> wrote... >Is there any way to copy and paste formatting from one cell to another >in Excel (2007 or otherwise)? I'm looking for an analog of ctrl-shift- >c / ctrl-shift-v from Word. I don't believe there's a [Ctrl]+[Shift]+C in Excel to copy just formats, but [Ctrl]+[Shift]+V will display the Paste Special dialog, and you could then press T and [Enter] to paste for...

Conditional Formatting 04-03-10
I have designed a spreadsheet to act as my Risk Assessment Register. It contains the weekending dates across the top of the sheet in B1:Z1, this is done by B1=08/01/10, C1=B1+7 and autofill to Z1. Obviously there will be 52 columns as there is 52 weeks in the year, I have only gone to Z1 for the purpose if this question. I have the date for risk assessment review down the left hand column in A2:A30 What I would like to do is conditionally format the cell that corresponds to the review date (A2:A30) depending on the weekending date in B1:Z1 Example If A2=16/01/10 then I would...

saving excel into text problem
I am trying to save an excel sheet text. The sheet is only one column. It looks like this the cells have data that looks like the following: md "User Name" The cells are formatted as text(also have tried general). When I save to text file, The text file column look like this: "md ""User Name""" It adds a bunch of extra quotes. How Can I avoid this? Thanks, Mike select the entire column and do a copy. Go to a good text editor and paste. Save your file -- Message posted from http://www.ExcelForum.com that is a better isdea than the solution I c...

I type text into a cell and get pound signs instead
I have free text fields in a spreadsheet I developed so co-workers can provide status updates on their work. Occasionally, when someone types in a status update, the cell only displays pound signs (################). If I select that cell, the text appears in the formula bar, but the active cell just shows pound signs. It doesn't seem to be related to the amount of text that was typed, because other text entries are longer but they display just fine in the cell. How do I fix this? Thank you. If there is nothing in the cell to the right it will usually run on across, if...

Sheet format questions -- Excel 2007
Two problems arise when I try to translate workbooks from Quattro Pro x4 format to Excel 2007. In QPRO I save as a XLS file. and then open it in Excel. Generally this works fine, however I find two problems: 1. The worksheets sometimes lack grid lines in Excel, though the Page Layout >Gridlines section of the ribbon has them checked. (Gridlines were showing in QPro) 2. If I copy all of one of these sheets lacking gridlines into the clipboard, open a new blank workbook, and paste, everything is black, because the background is black. I must go to the Ctl-1 menu and selec...

Text or number to short time.
I have linked an XLS file to a dataabse and I need to convert the following to short time Appt Time: 815 (Number), Return Time: 1455 (Custom hmm). I have no control how the xls file is formatted or what field are made available, I will use a query to select about 5 fields and uppned them to a table. Also I would like to convert Apt Date: 20070101 (Number) to date 01/01/2007. Thanks Quinto -- Message posted via http://www.accessmonster.com ...

Formatting Date from imported data
Hi~ I have tables that are automatically exported from Access on a monthly basis into excel spreadsheets-- the tabs are named after the tables that are imported - data imported gives monthly dollars figures - when I try to reformat the months from mm/dd/yy to mmmm - yy it does not format until I double click on each individual cell. Any way to go around this without having to click in each individual cell? Thanks in advance. SL SL, Insert a new column, format the new column for date, then use a formula like this in the first row of that column: =DATEVALUE(E1) Where E is the colu...

Exporting Tables to a Fixed Length text file
Using Access 2000 (no services packs) trying to export a table to fixed length txt file. The Access Table is sorted in the sequence that i NEED the text file sorted in, but after i perform the TransferText acExportFixed funtion, the resulting text file is in a different sequnece then my source table. Can anybody tell me if they've run into an issue like this and if there is a resolution to this problem in any of the service packs before i actually tell my customer to upgrade to the latest service pack or upgrade to a newer version of Access? Thxs in Advance! Hi Wayne, Access tab...

How do avoid 'Y' when deleting highlighted text in WORD?
I haveWORD 2003 and need to unset the option to prompt for a 'Y' everytime I highlight text for deleting and the ten push Del key. I need to find where to unset the parameter requiring this every time as I just want to highlight text for deletion and delete it wit a single key depress of DEL key From the Tools menu, select Options and then go to the General tab and uncheck the boxes for: Help for WordPerfect users and Navigation keys for WordPerfect users -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on...

Very Weird Date Format Problem
So far I have wasted over 3 hours on this silly problem. I have a report that is a monthly calendar display - a box for each day. To determine which day date to put in which box I find out the weekday of the first of the month. If it is Sunday (weekday 1) I put "1" in the first box (Weeks are displayed as Sunday to Saturday). I have a format value of d in each text box. The query uses a stored field which is the first of the month. That field is called StartDate. The query has a field called DayStart which is "DayStart: Weekday([StartDate])" This t...

Control formating
I would like the color of the control to change if the user has there cursor in it. How can I do that? You can use the OnEnter and OnExit events to change the color. Sarah at DaVita wrote: >I would like the color of the control to change if the user has there cursor >in it. How can I do that? -- HTH - RuralGuy (RG for short) acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via http://www.accessmonster.com Isn't there some way to make this happen throught the whole database with having to program each control? "ruralguy via AccessMonst...

Border Limitations for Conditional Formats
Unfortunately, we have observed that the conditional format feature appears to limit the choices for borders. For example, the user can select both solid and broken line borders, but cannot modify the thickness of the borders as you can when in the normal cell format dialog. Is there a way to specify a conditional format that specifies a thicker border? Sub thickborders() Select Case Range("a1").Value Case Is = 1: mc = 4 Case Else End Select With ActiveCell ..Interior.ColorIndex = mc ..BorderAround Weight:=xlThick End With End Sub -- Don Guillett Micro...