complex conditional format

I have a cell value based on a calculation,which gives me any one of 7 
values.The cell values are:-"DEAD","DEAD & DEAD","DEAD & LIVE","LIVE & 
DEAD","LIVE","LIVE & LIVE",BLANK CELL.  I would like to apply conditional 
format of that cell-"DEAD" word to red bold font 11 with  a color shade,and 
"LIVE" word to blue bold font11 with the same color shade.Since I can apply 
only three conditions  and a cell value can't accept two formats at a time,I 
am unable to do the task.Can any one suggest me how to do this.
0
10/31/2005 4:35:03 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
572 Views

Similar Articles

[PageSpeed] 56


-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
message news:32DFA54C-DC94-4AD0-BE44-99590BC39E31@microsoft.com...
> I have a cell value based on a calculation,which gives me any one of 7
> values.The cell values are:-"DEAD","DEAD & DEAD","DEAD & LIVE","LIVE &
> DEAD","LIVE","LIVE & LIVE",BLANK CELL.  I would like to apply conditional
> format of that cell-"DEAD" word to red bold font 11 with  a color
shade,and
> "LIVE" word to blue bold font11 with the same color shade.Since I can
apply
> only three conditions  and a cell value can't accept two formats at a
time,I
> am unable to do the task.Can any one suggest me how to do this.


0
bob.phillips1 (6510)
10/31/2005 9:13:35 AM
It can't be done. Using VBA you could get two different font colours, but
not with CF.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
message news:32DFA54C-DC94-4AD0-BE44-99590BC39E31@microsoft.com...
> I have a cell value based on a calculation,which gives me any one of 7
> values.The cell values are:-"DEAD","DEAD & DEAD","DEAD & LIVE","LIVE &
> DEAD","LIVE","LIVE & LIVE",BLANK CELL.  I would like to apply conditional
> format of that cell-"DEAD" word to red bold font 11 with  a color
shade,and
> "LIVE" word to blue bold font11 with the same color shade.Since I can
apply
> only three conditions  and a cell value can't accept two formats at a
time,I
> am unable to do the task.Can any one suggest me how to do this.


0
bob.phillips1 (6510)
10/31/2005 9:14:23 AM
beta freeware possibility:
http://www.xldynamic.com/source/xld.CFPlus.Download.html

"TUNGANA KURMA RAJU" wrote:

> I have a cell value based on a calculation,which gives me any one of 7 
> values.The cell values are:-"DEAD","DEAD & DEAD","DEAD & LIVE","LIVE & 
> DEAD","LIVE","LIVE & LIVE",BLANK CELL.  I would like to apply conditional 
> format of that cell-"DEAD" word to red bold font 11 with  a color shade,and 
> "LIVE" word to blue bold font11 with the same color shade.Since I can apply 
> only three conditions  and a cell value can't accept two formats at a time,I 
> am unable to do the task.Can any one suggest me how to do this.
0
nastech (103)
10/31/2005 11:28:01 AM
Thanks,but I am new to VBA ,can you give me simple VBA code to apply this two 
clors to this cell value

"Bob Phillips" wrote:

> It can't be done. Using VBA you could get two different font colours, but
> not with CF.
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
> message news:32DFA54C-DC94-4AD0-BE44-99590BC39E31@microsoft.com...
> > I have a cell value based on a calculation,which gives me any one of 7
> > values.The cell values are:-"DEAD","DEAD & DEAD","DEAD & LIVE","LIVE &
> > DEAD","LIVE","LIVE & LIVE",BLANK CELL.  I would like to apply conditional
> > format of that cell-"DEAD" word to red bold font 11 with  a color
> shade,and
> > "LIVE" word to blue bold font11 with the same color shade.Since I can
> apply
> > only three conditions  and a cell value can't accept two formats at a
> time,I
> > am unable to do the task.Can any one suggest me how to do this.
> 
> 
> 
0
11/2/2005 4:47:05 AM
Here is some code

Dim iPos As Long

    With ActiveCell
        iPos = InStr(.Value, "DEAD")
        .Characters(iPos, 4).Font.Color = vbRed
        iPos = InStr(.Value, "LIVE")
        .Characters(iPos, 4).Font.Color = vbBlue
    End With

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
message news:67A01644-99FF-4E7D-912C-7681C4E2F1CB@microsoft.com...
> Thanks,but I am new to VBA ,can you give me simple VBA code to apply this
two
> clors to this cell value
>
> "Bob Phillips" wrote:
>
> > It can't be done. Using VBA you could get two different font colours,
but
> > not with CF.
> >
> > -- 
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote
in
> > message news:32DFA54C-DC94-4AD0-BE44-99590BC39E31@microsoft.com...
> > > I have a cell value based on a calculation,which gives me any one of 7
> > > values.The cell values are:-"DEAD","DEAD & DEAD","DEAD & LIVE","LIVE &
> > > DEAD","LIVE","LIVE & LIVE",BLANK CELL.  I would like to apply
conditional
> > > format of that cell-"DEAD" word to red bold font 11 with  a color
> > shade,and
> > > "LIVE" word to blue bold font11 with the same color shade.Since I can
> > apply
> > > only three conditions  and a cell value can't accept two formats at a
> > time,I
> > > am unable to do the task.Can any one suggest me how to do this.
> >
> >
> >


0
bob.phillips1 (6510)
11/2/2005 9:15:16 AM
Thanks Bob,for testing purpose I have entered the code you have given,it gave 
me error 'invalid outside procedure'.I have all of my 7 cell values in a 
column and tested.
can you plese give me correct code.I have a column range with these 7 values 
which I have mentioned in my question.please give me event macro as my 
worksheet contains these values based on sheet calculation.Please remember 
there is a string '&' between these words in some cells which contain two 
words "DEAD" and "LIVE".Thanks in advance.
"Bob Phillips" wrote:

> Here is some code
> 
> Dim iPos As Long
> 
>     With ActiveCell
>         iPos = InStr(.Value, "DEAD")
>         .Characters(iPos, 4).Font.Color = vbRed
>         iPos = InStr(.Value, "LIVE")
>         .Characters(iPos, 4).Font.Color = vbBlue
>     End With
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
> message news:67A01644-99FF-4E7D-912C-7681C4E2F1CB@microsoft.com...
> > Thanks,but I am new to VBA ,can you give me simple VBA code to apply this
> two
> > clors to this cell value
> >
> > "Bob Phillips" wrote:
> >
> > > It can't be done. Using VBA you could get two different font colours,
> but
> > > not with CF.
> > >
> > > -- 
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote
> in
> > > message news:32DFA54C-DC94-4AD0-BE44-99590BC39E31@microsoft.com...
> > > > I have a cell value based on a calculation,which gives me any one of 7
> > > > values.The cell values are:-"DEAD","DEAD & DEAD","DEAD & LIVE","LIVE &
> > > > DEAD","LIVE","LIVE & LIVE",BLANK CELL.  I would like to apply
> conditional
> > > > format of that cell-"DEAD" word to red bold font 11 with  a color
> > > shade,and
> > > > "LIVE" word to blue bold font11 with the same color shade.Since I can
> > > apply
> > > > only three conditions  and a cell value can't accept two formats at a
> > > time,I
> > > > am unable to do the task.Can any one suggest me how to do this.
> > >
> > >
> > >
> 
> 
> 
0
11/3/2005 4:53:03 AM
Reply:

Similar Artilces:

calculating hours into date format
hi I would like a formula that will allow a user to input a figure (hours and minutes) into a cell that will calculate the end date and time based on a start date and time. For example, Enter 30 hours, static start time is 01/01/04 00:00hrs, the formula would then calculate that the end figure is 02/01/04 06:00hrs. Essentially it is calculating the number of hours & mins added onto the start date with the end result a date and time later than the hours & mins entered Hope this makes sense! Hi in A1 put your date in B1 enter your hours in the format hh:mm C1: =A1+B1 and forma...

mail format issues winmail.dat
I just got my iphone and Apple acknowledges problems with attachments sent from Outlook. I emailed my clients to make sure that the mail format options, are set to HTML and to double click my email address in contacts to change it to: Let Outlook decide. It works. However, I have 7 email addresses and sometimes I use an email which is not in my client’s address book. I they use reply, and I double click my email address in their reply, it shows up as rich mail format, which now explains to me why these emails show up as winmail.dat in the iphone. Even if I open these email us...

Keeping date in lower case format
Hi, I need to import some data from Oracle that is in ddmmmyy format i.e. 29jan73 The J in jan is lower case in oracle, but when i bring it to excel it formats it to uppercase (29Jan73) Is there any way I can stop this from happening, as I'd like it all to be in lower case? Thanks, Mandy. Hi Mandy, You CAN change it by going to Tools | Options | Custom Lists, and then edit Jan to jan. But then it will be changed for everything. Best regards, Kevin "Mandy Phillips" <phillipm@edgehill.ac.uk> wrote in message news:06d701c398a9$734061c0$a001280a@phx.gbl... > Hi...

Complex Query question
I've got a challenging query I need help with! Here's a snapshot of the tables and relationships. I've put the relevant tables into this query layout just for illustrative purposes. ----- click on it to make it bigger (it's still a little hard to read) http://tinypic.com/view.php?pic=6xu9oh4&s=1 Note - the table names start with phrases that are not relevant to this question (ie I use the word "record" for another use than the normal database usage of that word). ------ I'll explain the setup. I want the Table Record-Orders-Sales to lookup Record-Cur...

There is any auto email format?
Hi All, I know some one can help me in this regard, that I am using excel for Insurance expiry date list more then 2000 list I have. Now what my question is everyday I can’t able to find every expiry date. I want some solution from that if there is any formula like if insurance date was expired how can I get the email for me? Like the date is 8/3/2010 and number is 123456 at the same day automatically I want email from for warning so that I can renewal the same. If there is any formula or something pls help me. I am very struggling for this. Have you tried Conditional ...

date is in 20010129 format, how can I get it in m/d/y format
-- jsanders Data>text to column, go to step three and select date and YMD and for future posts it's considered rude just using the subject line Regards, Peo Sjoblom With the value 20010129 in cell A1, enter this formula in B1: =DATEVALUE(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4)) and the format the cell as a date. The various functions parse the 20010129 string into its components, and the DATEVALUE converts that string to a datevalue that Excel can recognize. ...

Conditional Formatting with Hyperlinks
OK, I have a hyperlink on tab 1 to a form on tab 2. The hyperlink is in red. What I want to do is get the the color of the hyperlink to change when just one word or a date has been placed in the form in tab 2 and not have the HL change because it's been clicked on. Pleaseeeeeeeee HELP!!!! ...

very complicated conditional formating #2
for some reason that is applying the conditional format to all selecte rows EXCEPT for the very bottom one. Weird -- lachlanmulle ----------------------------------------------------------------------- lachlanmullen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1029 View this thread: http://www.excelforum.com/showthread.php?threadid=27262 Hi this is the formula for the first row. -- Regards Frank Kabel Frankfurt, Germany "lachlanmullen" <lachlanmullen.1er8fa@excelforum-nospam.com> schrieb im Newsbeitrag news:lachlanmullen.1er8fa@excelforum...

Lotus 123 print, formula, format, ect
How can I get Excel to emulate Lotus 123 commands such as: print range, + - (formula) range, move information and calulatons. ...

Losing last zero while formating numbers to text
Ok, the situation is following .. i have text in csv format(comm delimited) ... (for example in cell A1 I have following text string 1.3456,1.5467,1.3559), and i need to change some number values include in this text. I extracted these numbers from text with mid() function converted to numbers format, and then made my changes. Now number look like 1.345(A1 cell), 1.5461(A2), 1.3553(A3).Then I need to conver these numbers back to the text and merge them in one text string an save as CSV FORMAT. The problem is when i try to convert for exampl number 1.345 to the text it converts like 1.345, bu...

Does Excel support mixed formatting of a string in a formula cell, yet?
Excel is one of the most spectacular apps ever created. But one feature I really need is mixed formatting of a string whose cell is a formula. By mixed formatting I mean, you know, making selected (as opposed to all) character(s) of the string bold, italic, a different color, sub- or superscripted, etc. In Excel 2002 you cannot do that in a formula cell (right?). In edit mode, the user would, say, press F9 to display the calc'ed string, selectively format it as usual, then cancel edit. Excel would save the formatting as a separate mask. What could be easier? The user would ...

in-cell dropdown formatting
In Excel I sometimes use data "validation" with a "list" to provide me with a drop-down list of possible entries for a cell, which can be quite useful for repetitive stuff. Unfortunately, I have not discovered how to control the font size within the drop down, and this occasionally becomes tiny and illegible. Does anybody know how to alter the drop-down text size? Typically I might have a list of 20 names. I don't think you have any control over the font size of the validation drop down list. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software ...

help choosing a chart format.
In my last job, we had a waterfall plot that would show time on the X axis, frequency on the y axes and the marker would change color according to magnitude of the signal. is there a good chart that will perform this type of plot? It is kind of like an XY scatter plot with a variable color. see peltiertech.com/Excel/Charts/Waterfall.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "fugazi48" <fugazi48@discussions.microsoft.com> wrote in message news:6A6088FA-C207-4CB2-A103-02CD9549D7E5@microsoft.com... > In my last job, we ...

How do I format publication design after page 18?
After I hit page 18 I could no longer format the publication design onto the page. Thought it might be considered the back cover but I even tried inserting pages and it still happened. Any suggestions? What formatting are you using? Text? When you setup your project did you use a wizard? What version of Publisher are you using? Have you tried, insert page, duplicate all objects on page XX? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "gw" <gw@discussions.microsoft.com> wrote in message news:91BA1FA9-6AA2-44C7-81D...

VLookup Returning N/A
Hello: I having a problem with a VLookup formula. My main spreadsheet is a download from a mainframe and the lookup table (on a separate spreadsheet) is inputted directly into Excel. The lookup value is a PO number. All the VLookup Formulas are returning N/A. The weird thing is that if I manually type in the PO Number in the main spreadsheet, then the formulas work. Or if I copy the PO Number from the main spreadsheet into the lookup table, then the formulas work. Obviously the PO numbers in the main spreadsheet and the PO numbers in the lookup table aren't matching, but I can't f...

how do I send graphical mail in html format?
I want to send an email having minimum 4 images to a mailing list. But it doesn't seem that easy, because I don't want those images as an attachment. How can I do it? does the list support HTML? If so, they should be embedded in the body. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Sol...

SQL to well formatted XML file via .NET
I am trying to create several .xml files, mirrored from my SQL database that are shaped according to an XSD schema. However, I have hit a brick wall! Droving through several materials on how to perform this function has resulted in some slightly jarring results! Basically, I would like to create a dataset.xsd XML map of a stored procedure from the SQL database, and through use of this .xsd pass the resulting correctly formatted .xml to a file (or heck even the /bin!) Here is a code that I have been working with to take the stored procedure view and pass it to an .xml file - to the /bin...

Currency formating problem #2
I am having trouble getting currency to format correctly. I cannot get the period to appear in us currency. I get $6 50 no matter what I try to change. I have uninstalled and reinstalled Office 2003 with no change. I have also installed all of the available updates. Any thoughts? Hi, Jerry; This is just a guess but at "Start> Control Panel> Regional and Language Options> Regional Options> Customize> Currency> Decimal symbol:" what do you see? Is there a decimal point in there? If not, does entering a decimal point, clicking OK, and restarting Excel fix i...

EXCEL chart legend formatting
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Can anyone tell me how to get superscript/subscript formatting to show in a chart legend? I've got it working in the excel data cells, but the formattin doesn't transfer to the legend and I can't directly change the legend text. I am having the same problem. Did you ever find a solution? Thanks! tauntonuse wrote: EXCEL chart legend formatting 28-Oct-09 Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Can anyone tell me how to get superscript/subscript formatting to show in a chart legend? I h...

Conditional Formatting #81
Hi All, I'm using Excel 2007, and am looking for a way to format the cells color if it matches a range of other cells. What I'm working on is a lottery sheet that allows me to enter the winning six numbers in six seperate cells. Then below these six cells, there may be several sets of six cells that I enter the ticket numbers in, and I would like to have any cells that match the winning cells to turn yellow. Below is my scenario. The winning ticket numbers will be entered in cells: E1, F1, G1, H1, I1, J1 Then the ticket numbers can be entered in any other cells, and when any cell m...

Formatting Times and converting to text
Hi, I have got a spreadsheet which has a column which holds a time calculated from another column. The times in both columns are in the format m:ss.000. The sheet when complete will be imported into Access which unfortunately xan't hold times with seconds with 3 decimal places so the recomended solution is to hold the time in a text field. So how in Excel can I convert the times into a text field without losing the currently displayed value, as whatever I try changes the value to the offset value (i.e. 7.88E-05 instead of 0:06.815). Thanks Graham Do it this way: =3DTEXT(A1,&q...

Subtracting two times in hh:mm:ss format
I need to subtract two times that are in hh:mm:ss format and they occur on different dates. Example: Time 1: 03/22/2005 19:09:47 Time 2: 03/23/2005 01:02:51 Result = Time 2 - Time 1 I've tried different things but I keep getting #VALUE! in the result cell. Any help would be appreciated. Thanks. Make sure the cells are correctly formatted as date and time. -- HTH RP (remove nothere from the email address if mailing direct) "A Khan" <A Khan@discussions.microsoft.com> wrote in message news:C179333B-FEDF-4542-8107-2F9B6AA6241F@microsoft.com... > I need to subtract...

Word Formatting
I need help with Word X. I have 2 documents that I am lately frequently trying to merge to a single PDF file. The files are a resume and a cover letter. I have the docs separate for most purposes, but occasionally want to create one file. Here's the problem.....when I try to cut/paste one into the other I loose most formatting (font size/spacing/colors) from the pasted document. I have tried all options in the "paste special" section to no avail..... Anyone have a workaround? Suggestion? MIKE T Well, to keep your formatting you would insert a section break before inser...

Font Format in Axis Labels
Is there a way to format font in axis labels? Like Bold, 12pt, Red "Phil Hageman" <PhilHageman@discussions.microsoft.com> wrote in message news:27C37738-83BB-45CA-9482-87060B0F5359@microsoft.com... > Is there a way to format font in axis labels? Like Bold, 12pt, Red Yes. Format Axis, and then the Font tab. -- David Biddulph Hi, If you want to format specific parts of the axis labels you will have to create the labels yourself. See Jon's examples http://peltiertech.com/Excel/Charts/axes.html#ArbAxis Cheers Andy Phil Hageman wrote: > Is there a way to for...

Date format 03-03-08
How do I get a date to print as mm-dd-yy in a field? On Sun, 02 Mar 2008 23:43:02 -0500, BC wrote: > How do I get a date to print as mm-dd-yy in a field? Set the format property of the Date control to: mm-dd-yy -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail ...