Find a vlaue, shade another cell

I would like to search column c in an excel sheet for any number that starts 
with '46'.  all the numbers in the column will be 7 digits long (eg. 4634567)

once found I would like to turn the font blue in the cell 10 columns to the 
right of this 46 cell.  

the macro should loop down column c until it can find no further values. 

any ideas if this is possible?
0
Utf
12/8/2009 5:44:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
817 Views

Similar Articles

[PageSpeed] 28

Hi,

Try this

Sub Sonic()
lastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set MyRange = Range("C1:C" & lastRow)
For Each c In MyRange
If Left(c.Value, 2) = "46" Then
    c.Offset(, 10).Font.ColorIndex = 5
End If
Next
End Sub


Mike

"Withnails" wrote:

> I would like to search column c in an excel sheet for any number that starts 
> with '46'.  all the numbers in the column will be 7 digits long (eg. 4634567)
> 
> once found I would like to turn the font blue in the cell 10 columns to the 
> right of this 46 cell.  
> 
> the macro should loop down column c until it can find no further values. 
> 
> any ideas if this is possible?
0
Utf
12/8/2009 5:53:06 PM
Hi

Try this:

Sub Color46()
FirstRow = 1
LastRow = Range("C" & Rows.Count).End(xlUp).Row

For r = FirstRow To LastRow
    If Left(Range("C" & r), 2) = 46 Then
        Range("M" & r).Interior.ColorIndex = 5
    End If
Next
End Sub

Regards,
Per

"Withnails" <Withnails@discussions.microsoft.com> skrev i meddelelsen 
news:C564CAC1-EBB4-4E52-A49F-753A7CF8481E@microsoft.com...
>I would like to search column c in an excel sheet for any number that 
>starts
> with '46'.  all the numbers in the column will be 7 digits long (eg. 
> 4634567)
>
> once found I would like to turn the font blue in the cell 10 columns to 
> the
> right of this 46 cell.
>
> the macro should loop down column c until it can find no further values.
>
> any ideas if this is possible? 

0
Per
12/8/2009 6:00:27 PM
Reply:

Similar Artilces:

How do I relate data of one document to another ?
When I imput additional information on a word document I would like that information transfered to an excel document, Likewise if I input data in an excel document I would like to have it transfered to a word document. How do I do it ? I don't think you can have the same link work both directions but you can copy and paste as link and when the source is updated (probably the excel file since I don't see the point of having Word as a source in Excel) the link will be updated as well -- Regards, Peo Sjoblom (No private emails please) "bandi" <bandi@discussions....

Move to match cells
A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 000801097-3 1998 11 000801091-7 009...

Duplicate Emails from one user to another
Hey, Ive got a weird one on my hands and can't figure out whats going on... We had a new employee start the other week, so I set him up a login, and an email address, setup the same as everyone else in the company, and everything seemed all hunky dory. Then one of our original employees starts telling me that he is recieveing duplicate emails from the new user. No one else recieves duplicates, and the only messages that do duplicate are from the new user (like, none of our emails duplicate to him). Ive checked his message rules (in case he's done something dumb like copy the...

Updating subform control based on another control value in the SAME subform.
Greetings, Calculated result is a field that is updated based on the two other fields, this is done in the subforms query and as based on an expression cannot be overtyped (which is good!). All I want to do is once a value appears in the field is to populate another field (see code below). Runs without errors but see no results. Any help would be appreciated. Private Sub CalculatedResult_AfterUpdate() Dim CResult As Integer CResult = Me.CalculatedResult.Value Me.ActualResult.Value = CResult Me.ActualResult.Requery End Sub Thanks Rob SOLVED "Rob W" <Whllrob@aol.com&g...

Formatting # in one cell
Can I separate numbers by a comma within a single cell so I can add, average and use the pivot function calculating each number separtly? I have the column formatted as text. In the cells I have: 2, 3, 4, 5 12, 2, 5, 24 There is one cell per row with the numbers and one column with these numbers. Thank you for your help. Brent30@cox.net Brent, You'll have to use Data | Text to columns, prior to doing anything with your numbers - as they are, Excel will treat them as strings. HTH, Bernie MS Excel MVP "Brent" <anonymous@discussions.microso...

Attach Email to a Cell
Hi, Is there any way to attach emails or other documents to cells? Thanks Try: CTRL+K (Insert > Hyperlink) Micky "Dave" wrote: > Hi, Is there any way to attach emails or other documents to cells? > > Thanks Hi, This wont work as I need to attach actual documents to the cell. Thanks "מיכאל (מיקי) אבידן" wrote: > Try: CTRL+K (Insert > Hyperlink) > Micky > > > "Dave" wrote: > > > Hi, Is there any way to attach emails or other documents to cells? > > > > Thanks If you say so.....

Excel won't highlight selected cells
Hi. Just started having a problem today with selecting cells. I have worksheets that need to have the formatting changed on certain cells. Some need to be merged, some need to have different fonts, etc. Before today, I was able to hold the CTRL key down and select whatever cells needed to be changed (as a group). I can still do that, but only the Title row and columns (A,B,C...1,2,3,4...) are highlighted but not the individual cells that I have clicked on. The problem is more of a large annoyance and I can still get my work done but it takes much longer because I can'...

format cell to search text in another cell
I'm trying to make it so that when text is typed in a cell, other cells will read it, and if the text includes certain letters (like "ss" in "dssl" or "ssdl") then it would insert "N/A" in the appropriate cells. i also want it to be able to look for more than one thing. Not case sesitive. Below is by no means code, but i don't know how else to explain it. For instance in cell C14 (IF C3 = "*ss" or "*bl1" then "N/A" else " ") I tried =IF(SEARCH("SS", C6,1)>0,"N/A", " "...

help importing cell
Hi can someone help me with the formula for importing the value of cell from one worksheet to another. For example the value of cell a1 from worksheet x to current sheet I believe the formula starts with ~ thanks in advance: -- eliosta ----------------------------------------------------------------------- eliostar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3251 View this thread: http://www.excelforum.com/showthread.php?threadid=52304 Hi You can use something like this: =sheet1!A1 in the cell to show what is in that cell on your current sheet. Andy. ...

How suppress chart points
I want to suppress chart points for a cumulative line graph of monthly telephone costs where the particular month's values have not yet been entered but there is a formula in the cell which currently returns null but will return the value once it is posted on the accountmaster sheet. Hi, Use NA(). The will cause the data marker to be omitted. Note the line will be interpolated between valid data points. Something like this, =IF( <test> , <value> , NA() ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "sarcastix" <sarcastix@di...

Cell formatting
I am using a formula to copy cells from one sheet to another. Everything is ok except the date column. The when that cell is "" or -0- it shows 1/0/1900 How can I change this to "-" or blank? Many thanks One way: =IF(Sheet1!A1>0,Sheet1!A1,"") In article <1105828404.988014.300140@c13g2000cwb.googlegroups.com>, "seve" <scooksey@charter.net> wrote: > I am using a formula to copy cells from one sheet to another. > > Everything is ok except the date column. > > The when that cell is "" or -0- it shows 1/0...

Find file using first 2 digits
How can I search for a file using the first to digits of the filename? -- aking1987 ------------------------------------------------------------------------ aking1987's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15393 View this thread: http://www.excelforum.com/showthread.php?threadid=319299 Manually: Windows start button|Find (or Search) Search for XY*.xls (change XY to what you want) You can also use this technique in the File|open dialog. aking1987 wrote: > > How can I search for a file using the first to digits of the filename? > >...

Formatting cells
I have a seniority roster of personnel. It has five colums and 30 rows. The borders of the cells are formatted.....some thin some bold. Lets say that today, there are 15 rows with info in them to represent 15 people . I now need to move the bottom 5 rows down one row to make room for another person in proper seniority position. When I cut/paste the 25 cells I lose some of the formatting and have to go back and insert lines where they have disappeared. How can I lock the formatting of the border while still being able to edit the cell content? Instead of cutting and pasting simp...

How to move DB files to another drive
Hi, I'm running Exch. 2003 on windows 2003 server and just added a second hard drive and would like to move my log files to that drive. Can someone tell me how to do that. I remember in Exch. 5.5 there was a wizard to do that but I can't find it in Exch. 2003. Thanks for your help. Clayton Right Click the storage group and go to properties. You will find it. On Sun, 30 Jan 2005 17:16:46 -0600, "Clayton Sutton" <none@none.com> wrote: >Hi, > >I'm running Exch. 2003 on windows 2003 server and just added a second hard >drive and would like t...

Ref cell in another worksheet
Hi All Sorry for the very basic question. I have several worksheets in a workbook and have named the first sheet 'Info'. This sheet holds such data as 'contract number' and 'user name'..... This data is required on all the other sheets but i would like to just reference the data from the 'Info' sheet What do i have to enter on the other sheets so that this info only has to be entered on the 'Info' sheet How do i reference this data/info from the 'Info' sheet in the header/footer of printed sheets Thanks for any/all help Sal Select the sheet...

Linking cells to MS Project cells
I have an Excel file with cells linked to MS Project cells. There are several MS Project source files involved. When first created all the Excel links worked as expected. Upon reopening the Excel file some cells update properly and others display #NAME?. Does anyone know why and is there a way to fix this? To make matters a bit more confusing I have seen(on rare occasion)upon reopening the Excel file some cells previously displaying the #NAME? value displaying appropriate linked data correctly. ...

displaying a name in a cell reference
I have two worksheets in an Excel workbook. Sheet1 has a prize designation column with different prizes i.e. WM20, WM10, etc Sheet2 has the prize designation (WM20) matched with a name i.e. Walmart giftcard I need to display the name from sheet2 in a column on sheet1? Thanks You could use a vlookup function to do that for you. Something like this: =VLOOKUP(B6,Sheet2!$A$1:$B$29,2,FALSE) Assuming B6 contains “WM20” Assuming the range on sheet2 A1:B29 contains WM20 in column A and the description “Walmart 20 buck Gift certificate etc” in it. Hope that helps. Fran...

Merge the same group of cells down a column quickly
I've got 900 rows of data in which each needs to have cells 1:3 merged and centered (so, a1:a3, b1:b3, c1:c3, etc). Is there any fast way of doing this besides selecting the same three cells in each row, hitting Merge & Center, and then moving on to the next row? Trying to create a macro has been fruitless and quite frustrating. Im using 2007. Thanks in advance! -- Rich F in the name box put a1:c900. then "Merge Across", then r-click selection and format cells, alignment Horizontal Center. Rich F wrote: > I've got 900 rows of data in which ea...

Manually Posting Monthly Draws from a Another Account
I am trying to determine the best way to post a monthly draw amount in a mutual fund cash account (share price always $1) that occurs on the 5th of each month. Money automatically picks up the deposit in my checking account, but I need to post the change in the mutual fund so that it shows up correctly for reporting as a reduction for the fund. It would be nice if I could have Money do it automatically each month, as well. I currently have entered the draw as a "remove shares" type transaction, but I am not sure if that is the correct way. -- --garyr You need to enter som...

How to refer to current cell in range
This is a simple problem, but after a couple hours of searching Excel help and Newgroups, I can't seem to find anything helpful. In Excel 2000 I've been using the countif function to count cells that contain a certain phrase, e.g. this counts the cells in the range that contain the phrase "W2K Pro": =COUNTIF(PCs!D2:D143,"W2K Pro") What I'd like to do is count cells in which the LEN() function returns a value greater than 12, so something like this: =COUNTIF(PCs!D2:D143,LEN(currentcell)>12) I just can't seem to find a way to refer to the current cell ...

Updating Chart Titles from Spreadsheet Cells
I'm using Excel 2002 and I want to be able to have the titles in my charts update based on entries in the associated spreadsheet. On a monthly basis I import data into a spreadsheet template that has a chart associated with it. It would be nice if the title block on the chart would update based on a cell in the spreadsheet. --- Message posted from http://www.ExcelForum.com/ Hi have a look at http://www.tushar-mehta.com/excel/newsgroups/dynamic_chart_title/index. html HTH Frank > I'm using Excel 2002 and I want to be able to have the titles in my > charts update based on ...

find closest match to a reference number in a row of numbers
How can I find the closest match larger( or smaller) than a reference value in a row of unsorted data Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: A5 = lookup_value =INDEX(A1:J1,MATCH(MIN(ABS(A1:J1-A5)),ABS(A1:J1-A5),0)) Biff "Nick Krill" <Nick Krill@discussions.microsoft.com> wrote in message news:48E40D61-4180-43FC-B9D9-293C462F1AD5@microsoft.com... > How can I find the closest match larger( or smaller) than a reference > value > in a row of unsorted data Biff: This is very nice. Can you explain or interpret how the formu...

Compare one year to another, but just YTD
Let's see if I can explain this. I have two sheets, the first sheet is labeled 2005 and the second one is 2006. In A1 thru A12 on both sheets is Jan, Feb, Mar, etc. On 2005 B1 thru B12 we have monthly values. On 2006 B1 thru B7 (as we are just now going into Aug.) we have monthly values. In B13 on both sheets are totals. The problem is I can't compare on year to the next as the total on the 2005 sheet is for the full year. I'd like to add Label in A14 that says 2005 YTD and have a formula in B14 that looks at how many cells in the range B1 thru B12 on 2006 have values then...

Extract numbers from cell with Text and Numbers
Hello All, I hope someone can help. I have a column of cells. Each cell contains some text, and then a number in parenthases, then a comma, and then come text, and then a number in parenthases, This repeats probably as many as 5 times, though some cells have fewer then 5. I want to somehow, if possible, extract JUST THE NUMBERS. If I can automatically get a total so that I can average the total (dividing by a number in another column, but in the same row as the numbers extracted) that would be great. I am most concerned with not having to manually enter the numbers again. If anyone ha...

Excel: too many different cell formats
I am plagued by this error in a large multi-paged workbook in Excel 2000. Is there a workaround? Will a later version have a better tolerance? Check out this Jack XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?scid=kb;en-us;213904 David McRitchie posted this Leo Heuser posted a macro 2001-05-06 in programming as a very major revision of the macro in the eee007 article. http://groups.google.com/groups?selm=OxP9cgi1AHA.1572%40tkmsftngp02 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Jack" <Hig...