Conditional Formatting - part of cell only

Is it possible to format a portion of a text string within a cell (as opposed 
to the entire cell). For example, I would like to format the word 'gift' in 
red font anywhere it a appears in range C2:C417 but only that word, not the 
entire cell.
0
RobDDrums (1)
1/9/2006 10:47:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
563 Views

Similar Articles

[PageSpeed] 25

Not with conditional formatting.

But you could change the actual format for that word (or group of characters)...

Saved from a previous post (or two!):

If you want to change the color of just the characters, you need VBA in all
versions.

You want a macro????

Option Explicit
Option Compare Text
Sub testme()

    Application.ScreenUpdating = False

    Dim myWords As Variant
    Dim myRng As Range
    Dim foundCell As Range
    Dim iCtr As Long 'word counter
    Dim cCtr As Long 'character counter
    Dim FirstAddress As String
    Dim AllFoundCells As Range
    Dim myCell As Range
        
    'add other words here
    myWords = Array("widgets")
    
    Set myRng = Selection
    
    On Error Resume Next
    Set myRng = Intersect(myRng, _
                  myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
    On Error GoTo 0
    
    If myRng Is Nothing Then
        MsgBox "Please choose a range that contains text constants!"
        Exit Sub
    End If
    
    For iCtr = LBound(myWords) To UBound(myWords)
        FirstAddress = ""
        Set foundCell = Nothing
        With myRng
            Set foundCell = .Find(what:=myWords(iCtr), _
                                LookIn:=xlValues, lookat:=xlPart, _
                                after:=.Cells(.Cells.Count))
                                
            If foundCell Is Nothing Then
                MsgBox myWords(iCtr) & " wasn't found!"
            Else
                Set AllFoundCells = foundCell
                FirstAddress = foundCell.Address
                Do
                    If AllFoundCells Is Nothing Then
                        Set AllFoundCells = foundCell
                    Else
                        Set AllFoundCells = Union(foundCell, AllFoundCells)
                    End If
                    Set foundCell = .FindNext(foundCell)
                    
                Loop While Not foundCell Is Nothing _
                    And foundCell.Address <> FirstAddress
            End If
                    
        End With
        
        If AllFoundCells Is Nothing Then
            'do nothing
        Else
            For Each myCell In AllFoundCells.Cells
                For cCtr = 1 To Len(myCell.Value)
                    If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _
                              = myWords(iCtr) Then
                        myCell.Characters(Start:=cCtr, _
                                            Length:=Len(myWords(iCtr))) _
                                               .Font.colorindex = 3
                    End If
                Next cCtr
            Next myCell
        End If
    Next iCtr
    Application.ScreenUpdating = True

End Sub

This line:
                        myCell.Characters(Start:=cCtr, _
                                            Length:=Len(myWords(iCtr))) _
                                               .Font.colorindex = 3
changes the color.



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


RobDDrums wrote:
> 
> Is it possible to format a portion of a text string within a cell (as opposed
> to the entire cell). For example, I would like to format the word 'gift' in
> red font anywhere it a appears in range C2:C417 but only that word, not the
> entire cell.

-- 

Dave Peterson
0
petersod (12005)
1/9/2006 11:11:01 PM
Reply:

Similar Artilces:

connecting cells with connector lines
Hello! Is it possible in Excel (2007) to draw a connector line (with or without an arrow ...) between two cells in a worksheet, so that the line-tips will follow their cells even when the latter are moved ? Thanks Michael On Feb 25, 7:00=A0am, Michaelprem123 <michaelprem...@gmail.com> wrote: > Hello! > > Is it possible in Excel (2007) to draw a connector line (with or > without an arrow ...) between two cells in a worksheet, so that the > line-tips will follow their cells even when the latter are moved ? > > Thanks > > Michael The line will automatically a...

Format Question: want veritical letters that "read" downward
Hi: I want to format a cell so that the letters up "normal" but are stacked vertically. Thus: H e l l o I can get format to rotate the word 90 degrees but that's not quite what I want. (I did this in 123, want to do it in Excel.) Thanks jlg Check Format/Cells/Alignment and click on the vertical text button in the Orientation section. In article <OAyEoJ9dDHA.2332@TK2MSFTNGP11.phx.gbl>, "King George John" <gilmer@crosslink.net> wrote: > Hi: > > I want to format a cell so that the letters up "normal" but are stacked > verti...

Summing items in a cell
How do you SUM items in a cell. I have a parts list with reference designators like U3-U6. I want the sum of the range of items, in this case it's 4, but can't figure out how to do this. Any help will be appreciated -- dpmoore ------------------------------------------------------------------------ dpmoore's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24920 View this thread: http://www.excelforum.com/showthread.php?threadid=384566 More info and then a formula using find can be used or a udf (macro for custom formula) -- Don Guillett SalesAid Soft...

2 different fonts in the same cell?
Hello! If I want (Times New Roman) "This is a red Wingdings 3 triangle ([change font] Wingdings 3: 123 [back to Times New Roman]").", I can do that in a cell. But if I refer to that cell (=A1) in B1, the Wingdings font doesn't come over and it winds up all TImes New Roman and looks like "This a red Wingdings 3 triangle ({)." How can I "=A1" for a cell that has two fonts in it and have the two fonts show up?? Thanks! VR/ Lost You can't have that in a FORMULA. You would have to change it to text first. -- Don Guillett Microsoft MVP Excel Sales...

How to tell Excel to insert cells and shift down from Access
Hi all, I have Access 2003. I am trying to tell Excel to insert cells and shift cells down from Access, with this line of code: oWksh.Cells.Insert Shift:=xlDown But Access does not like that line. It gave me run-time error 1004. I know if has to do with the part Shift:=xlDown Can you suggestion what I can do? Thank you in advance, Ben "Ben" <Ben@NoSpam.com> wrote in message news:eSZuzcDpKHA.1892@TK2MSFTNGP02.phx.gbl... > Hi all, > > I have Access 2003. I am trying to tell Excel to insert cells and shift > cells down from Access,...

EXCEL Cell Formatting: Custom or Conditional
At work I have Excel 2002 and I have some cells that I import data in from the mainframe. I have a table where there is a column for GENDER (MALE or FEMALE) that I would like to see shown as "Male" or "Female" (mixed case). Is there a way to change all upper case to mixed case using either CUSTOM formatting or CONDITIONAL formatting? I would prefer not using a macro if there is something simpler. I know that =PROPER() produces the correct result, but I don't see how I can use this unless I'm referencing another cell. I would like to be able to just change what fa...

ClearContents with condition
Hello, Using Excel XP. I have the following range in Sheet1: Range E1: H downwards (b) = Blank cell DESC CODE PURCHASE1 PURCHASE2 Widget S101 (b) $100.00 Widget S102 (b) (b) Widget S103 $250.00 (b) Widget S104 (b) (b) Widget S105 $325.00 (b) I am trying to devise a macro to clear the description cell (column E) and the code cell (column F) if both cells in columns G and H are blank. So, I end up with: DESC CODE PURCHASE1 PURCHASE2 Widget S101 (b) $100.00 (b) (b) (b) (b) Widget S103 $250.00 (b) (b) (b) ...

covering part of a text box border
I want to have a large text box with a border...I then want to place a smaller text box within the larger one (with its own border). However, some of the border of the large text box is vissible within the small text box. Is there a way to set the small text box on top of the larger one and partially cover the large text box border? I can not find an answer to this problem after many searches. set the smaller box on a higher layer and set a fill color on the = smaller box. "Willie Birdie" <Willie Birdie@discussions.microsoft.com> wrote in = message news:95B71C45-E4BD-...

displaying dates in UK format
I didn't write the original database and my knowledge of database design is not good enough to duplicate the work already done. With a variety of computers running Windows98 and/or XP, using Access2003, I cannot get dates to display on my forms in UK format (i.e. dd/mm/yyyy or even d/m/yy). The computers in use all have the short date format set to dd/mm/yyyy in regional settings. I have read through http://allenbrowne.com/ser-36.html and tried the formatting suggested there and it didn't work for me. I added it to "Lost focus" and "OnExit" and ...

I want a formula to ignore text values in cell references
If text is input into some cells referenced by a formula I want the formula to ignore whatever text it finds and just calculate the result of the numbers in cells. How do i do this? Presently the formula displays #VALUE! where there is text in one or more of the cells referenced. Use ISNUMBER(): In place of A1 use =IF(ISNUMBER(A1),A1,"") -- Gary''s Student "Russellrupert" wrote: > If text is input into some cells referenced by a formula I want the formula > to ignore whatever text it finds and just calculate the result of the numbers > in cells...

Publish part of Outlook agenda on Google
Hi, (One newbe can ask more than 1,000 wise guys can answer. This will be an example. Although: you all know a lot.) I want to publish a part of my Outlook agenda in the Google agenda. 'Part of' means: - only working days - between, say 08:30 and 18:00, so working hours - perhaps some specially marked appointments What I can think of: - GSyncIt to do the job. - But does GSyncIt allow the time slot? (I just found the program.) - Or create a second Outlook agenda, filled with the appropriate appointments, using VBA and then use GSyncIt. - Anything else ... Kind regards, Frans F...

Official name of the File Selection Part of CFileDialog
I want to know the official name of the file selection portion of the CFileDialog. I also want to know how this is implemented, is there a special kind of ListBox that can do multiple columns? "Peter Olcott" <NoSpam@SeeScreen.com> wrote in message news:Kw%3h.10768$IC.9812@newsfe21.lga... >I want to know the official name of the file selection portion of the >CFileDialog. I also want to know how this is implemented, is there a >special kind of ListBox that can do multiple columns? List-View http://msdn.microsoft.com/library/en-us/shellcc/platform/commctls/list...

in Excel: How do I change "selected cells" highlight color?
When I select cells in Excel the shading is so light (blue) that I can barely tell which cells are selected from those that are not. Can I change the background color of cells that are selected? Thanks. Assuming Excel 2007 this is a known problem and as yet no patch for it. I don't have Excel 2007 but I have read that playing with your Contrast can help some. Gord Dibben MS Excel MVP On Tue, 10 Jun 2008 12:51:00 -0700, flameretired <flameretired@discussions.microsoft.com> wrote: >When I select cells in Excel the shading is so light (blue) that I can barely >tell whic...

How to make A1 the active cell in all visible worksheets ?
This is a multi-part message in MIME format. ------=_NextPart_000_0028_01CA8164.76257740 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable ......so that when i activate the sheet A1 is the cell in the upper left = corner of my screen. Thanx ------=_NextPart_000_0028_01CA8164.76257740 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content=3D"text/html; ...

Conditional Formatting dependent on whether cell is populated.
I want to conditionally format cell G2 dependent upon whether there is a value in cell C2 AND G2 Therefore, if cell C2 has a value in it and cell G2 doesn't then cell G2 color = Amber if cell C2 has a value in it and cell G2 has a value then cell G2 color = Green Any helpful hints or useful suggestions? Thanks Tel Conditional Format of G2, condition 1: =AND(COUNTA(C2)=1,ISBLANK(G2)) format Amber condition 2: =COUNTA(C2,G2)=2 format Green -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tel" wrote: &...

Lookup for Part of the number
Hi, I need to lookup for a Loan Number from another sheet. However the problem is sometimes one digit would be added to the end in the other worksheet. Is there a way to find match for this digit.For the digits in below list I need to get the match as true for 12345 and 12346 as there is 123451 and 123462 in 2nd Sheet. List in 1st Sheet Loan# 12345 12346 12347 List in 2nd Sheet Loan# 123451 123462 234728 =MATCH(A2&"*";Sheet2!A:A;0) Format both column like text! -- Regards! Stefi „Kiran” ezt írta: > Hi, > > I ne...

count conditional days
I have created data as below: A B C D E F Sun Mon Tue Wed Thu Fri Sat.... 1 2 3 4 5 6 7 .....31 Jack P P A P A P A P I want to calculate how many Sundays, Tuesdays etc. Jack was present and absent? How can I do this? (P=Present A=absent) Ajit -- Knowldege is Power =SUMPRODUCT(--(A3:A10="Jack"),--(OFFSET(A3:A10,,1)="P")) Change the name to suit, the A/P to suit, and the ,,1 to reflect the day -- HTH RP (remove nothere from the email address if mailing direct) "Aj...

eliminating blank cells
I have two sheets in a workbook. Say sheet January and Summary. Sheet January looks like this DATE SN Amount TAX OVD CBS 12.01.2007 483 3464 1559 701 1184 13.01.2007 484 3907 1758 791 -627 14.01.2007 485 241 108 49 -75 17.01.2007 9/A12 1623 730 329 104 19.01.2007 11/A2 329 839 292 88 Now I want to gather these information in sheet "Summary" eliminating the blank rows, so Summary looks like this: DATE SN Amount TAX OVD CBS 12.01.2007 483 3464 1559 701 1184 13.01.2007 484 3907 1758 791 -627 14.01.2007 485 241 108 49 -75 17.01.2007 9/A12 1623 730 329 10...

custom header date formats
How can I set the date format on a custom header? I know I can use &[date] to insert the date...the problem is I want the date to display in a specific format (ex: MMDDYY or MMDDYYYY) Thanks! Ron --- Message posted from http://www.ExcelForum.com/ Use something like: & TEXT(Today(),"d-mmm-yy") HTH Otto "Ron777 >" <<Ron777.102ysm@excelforum-nospam.com> wrote in message news:Ron777.102ysm@excelforum-nospam.com... > How can I set the date format on a custom header? I know I can use > &[date] to insert the date...the problem is I want the...

Correctly copy cells with a macro
I have a form that after it is filled out I would like to transfer the information (with a macro button) into an ongoing list on another page. Can I program a macro to paste the information on the worksheets next available line since this number will change everytime I use it as the list grows larger with every entry? Hi Amy See this page http://www.rondebruin.nl/copy1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Amy" <Amy@discussions.microsoft.com> wrote in message news:469D477A-C72D-47F8-970F-45256CFA5EE2@microsoft.com... >I have a form that after it i...

in sheet search query to search sheet for a given cell.
I am setting up a spreadsheet to be used as a floor plan for a call center. the floor plan has over 500 stations and I am trying to create a query witin the spreadsheet that will allow a user to enter a station number and it will display the location on the floor plan. I do not want the user to be able to use the find function on the tool bars...I just want a cell that a location can be entered and then the location will be highlighted on the sheet. Any thoughts would be appreciated. You can use conditional formatting to highlight the station. For example, if the user types the statio...

format change on Copy sheet
Help needed! I am getting data format corruptions on move or copying a sheet. My sheet has data, as chart and some formulas. After Copyig a sheet dat formats display like # #### #### and change to left justified including on some other sheets that have nothing to do with what I am doing. How can this happen? How can I prevent it. I could like to email someone the file to take a look at. Glenn Glenn, It sounds as if you're selecting cells, and copy/pasting. Maybe copying the entire sheet would do better. Right-click the sheet tab, Move or Copy, etc. If your formulas refer to stuf...

Availables : More that 3500 Solutions manuals and Test Banks (Part 1)
List of Solutions Manuals _________________________ contact me to : mattosbw1@gmail.com mattosbw1(at)gmail.com NOTE : "THIS SERVICE IS NOT AVAILABLE FOR : CHINA, INDIA, PAKISTAN, IRAQ, IRAN, PHILIPPINES, NORTH KOREA, NEPAL, BANGLADESH, SRI LANKA, MALDIVES & BHUTAN". If your wanted solutions manual is not in this list, also can ask me if is available (They are some only). Then if you need solutions manual only contact me by email. This same "list of titles" of texts with availables solutions manual is for download from...

using a UDF in place of a cond. format, b/c I need 4, not three
I know this is an age old topic here, and I'm really hoping that I don't just get referred to another web page [that type of help of course has its place and merits]. And if I had the time, I would have more fun going through docs and notes to find out how to do it, but time is not something I can barter. What I'm looking at is: I'm thinking along the lines of an example where I normally set the borders of my columns [except if it's the first row] in a cond. format via something like this: =AND((NOT(ISBLANK($A1))),(ROW()>1)) But, if I need that space for a Con...

chart formatted for Weeks, not Days
I am trying to graph some data in Excel 2002 SP3. Column A contains dates (1 date every 7 days) and Columns B through Z the actual data. On my graph I wish to change to eliminate the extra spaces between data points because every day is displayed, not just the weekly dates entered. The only options in the Format Axis I can find are Days, Months, Years. Where is the Weekly option ? Any help is greatly appreciated. Thanks ! ...