Matching cells by content then cell fill with color

Thanks to JEM, I am using this routine to color three consecutive cell
a specific color, in this case red:

Public Sub ThreeCellsRed()
ActiveCell.Resize(1,3).Interior.ColorIndex = 3
End Sub

What I need now is a way for the routine to continue to find all th
similar cells, let's say for sake of disc they are people's names, s
when I execute the above on my name, mrh, I want it to continue in th
worksheet and find all exact matches and color those same cells red.

Another thought, say my name (MRH) is in "A1" and it is also in "D1".
But in "D1" I use "=A1".  Can I script a formula around "=A1" tha
would automatically fill the cell red when the above routine i
executed?

Ideas? Thx in advance, MR

--
Message posted from http://www.ExcelForum.com

0
9/1/2004 4:56:36 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
723 Views

Similar Articles

[PageSpeed] 47

this code will work for a selected area. for example, select the regio
between A1 to D4 and run this code. it will colour all the cell wit
the string "mrh".

Option Explicit

Sub ColourCellsRed()

Dim rngCell As Range
Dim rngAll As Range

Set rngAll = Selection

For Each rngCell In rngAll

If rngCell.Value = "mrh" Then rngCell.Interior.ColorIndex = 3

Next rngCell

End Su

--
Message posted from http://www.ExcelForum.com

0
9/1/2004 5:44:37 PM
Reply:

Similar Artilces:

Solver Limits for Constraints and Adjustable Cells
What is Solver's limits for constraints and adjustable cells? According to Microsoft Knowledge Base Article 75714 it is: 200 adjustable cells, and unlimited constraints for linear problems and 500 for non-linear problems for Excel 97, 98 and 2000. According to Frontline Systems, the developers of the Excel Solver, the limits are 200 adjustable cells, and 200 constraints for linear problems and 100 for non-linear problems. They do not refer to a specific version of Excel. There is a significant difference (in terms of the number of constraints) between the above two piece...

Copy and Paste in Excel, copies cell and formula, but shows same v
Excel 2003 with 2007 converters installed. Not constant problem, but have seen it once before, can't remember how to stop it happening. I have a bank forecast which I have a running total column, against a individual line item list. Noddy stuff. Problem. If I make changes to the forecast I need to recopy down the running total formula to refresh the running total when it doesn't pick up the additions, usual when a cut has been pasted in. Whilst it allows me to dragdown or copy and paste the formula and the new cells take on the correct line/column numbers to do thei...

Adding the contents of cells from other workbooks
Dear All I am trying to develop a tour sheet which has values in given cells. I have created a master tour sheet in another workbook so that when you input numbers on the original worksheet they transfer overto the master workbook. I want to be able to transfer data to the master workbook from multiple, separate worksheets/books (other people will have copies of the worksheet and fil it in.) This is where i am struggling is how can i get the data to add when other sheets are filed in as at the moment it just replaces the original data in the master copy instead of adding to it. Hope i have e...

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <donna@yahoo.com> wrote in message news:BE688A2B-3D42-45A0-AA3F-B65034C685E1@microsoft.com... > > I am using this for...

How do I preserve text color when combining cell data
I'm using "&" to combine data from multiple cells. Each cell has text of a different color and I want to preserve those colors in the new combined cell. The new combined cell is formatting all the text to one color. Anyone have any suggestions on how to maintain the separate colors in the new cell? A formula can only return a value to a cell, so you cannot return formatting and such. =A1&A2 will return the value of A1 and A2 to the cell that has that formula entered in it. HTH Regards, Howard "CraigS" <CraigS@discussions.microsof...

Borders Don't Line up with Cells.
Hi, I have a spreadsheet where I have used borders of different types to highlight cells. However, recently the horizontal borders aren't working correctly. The lines don't match the text. The spacing of the line is bigger than the spacing of the text, and so the lines run through the text. Does anyone know what causes this and how I can fix it? I have tried two different printers and have the same problem, so I don't think it is a printer driver problem. Thanks. Michael ...

Client E-Mail and Web E-Mail not matching
I've got a few complaints from about 4 users where e-mail they see in their Outlook Client is not showing up in their Outlook Web Client when they check their e-mail from a remote location. We're using Exchange 2003 with SP2 installed and all the users have Office Outlook 2003 with SP1 Installed also. This seems to only have been a problem since we upgraded to Exchange SP2 two weeks ago. (Although I can't confirm it, but I wasn't told of any problems before th upgrade.) We had to upgrade to SP2 because of the 16 Gig limit so rollback is not an option. Best as I can t...

not wordwrapping last two lines in cell
In cell 3C I have 18 lines that will wordwrap. the last two sentences will not wordwrap the last two sentences list about 10 words each and then run off the cell and cannot be seen. Any ideas how to fix this Thanks in Advance Does this link help: http://support.microsoft.com/default.aspx?scid=kb;en-us;211580 "badgercat" wrote: > In cell 3C I have 18 lines that will wordwrap. > the last two sentences will not wordwrap > the last two sentences list about 10 words each and then run off the cell > and cannot be seen. > > Any ideas how to fix this > > Tha...

copy & paste spreadsheet cells from excel to outlook to excel
I am trying to copy an example of 2 rows of 5 cells each into outlook to e-mail to 300 people so they can put info into 5 of the cells and reply back to me. When I receive this e-mail back I want to copy those 5 cells into a spreadsheet that already exists replacing what is there with this new information for each person. I have tried several methods of copy and paste but each time I paste back into excel I get two rows of cells, one row of empty cells and one with the new data. How can I avoid getting 2 rows of 5 cells when I am copying only one row of 5 cells? It is maddening! Send t...

Wrapping text in a cell
In a single cell, suppose I want text to appear on two lines. Viz: Case One Case Two How do I do that so that I specify the wrap point? Thanks! If you are typing the data into the cell use Alt-Enter between each string to indicate where you want a line break to occur. Case One<Alt-Enter>Case Two Alt + Enter -- Lilliabeth ------------------------------------------------------------------------ Lilliabeth's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27741 View this thread: http://www.excelforum.com/showthread.php?threadid=476428 If you...

Filling a Cell with Color
I know how to use conditional formatting to change the text color in a cell. But I'm at a loss as to how to make a cell "fill" with a color. It must be something simple. You should still be able to use Conditional Formatting... After clicking the [format...] button, there are 3 tabs: • Font • Border • Patterns<-------Use this one Does that help? *********** Regards, Ron XL2003, WinXP "RF" wrote: > I know how to use conditional formatting to change the text color in a > cell. But I'm at a loss as to how to make a cell "fill" with a color....

Excel 2002 : How to eliminate _ before a cell address in a formula
Hi, I am working on a report generated by a business system In Excel Format. The table in the Excel file is a Sub Total Table that is expandable. I need to manipulate on the data of the expanded table, however noted that the formula below could not work as I copy downwards. The second and third argument of the formula is fixed by "_" before the cell address E17 and D17. =IF(LEFT(P10,1)="5",_E17,-_D27) Also I find that when I move the cursor to the cell for the 2nd and 3rd argument, the resulting cell address that appears in the formula is not the...

Matching cells #3
Here's my spreadsheet: A B C 1 1 1 2 3 3 3 4 6 4 5 10 5 8 11 6 9 7 10 8 9 10 11 12 13 How do I get the matching numbers on the same rows, like: A B C 1 1 1 2 3 3 3 4 4 5 5 6 6 7 8 8 9 9 10 10 10 11 11 12 13 13 I would just re-create the columns. 1. Copy column A into D. 2. In E1 put: =IF(COUNTIF(B:B,$A1),$A1,"") 3. Copy it over to F1 and then down as far as needed. 4. Select columns D thru F, copy them, and go to Edit > Paste Special > Value. Press OK. 5. Delete column A thru C. HTH Jason Atlanta, GA &g...

How do I count one cell based on another cell?
How do you count the number of cells with data (not blank) in a given range based on a criteria in a different range? for example, how many corresponding cells are populated with data in c1:c1000, if b1:b1000 = SMU? I have tried so many variations of the the following to no avail. =COUNT(IF(b1:b1000,"SMU",counta(c1:c1000))) If anyone can resuce me...I am frustrated...what am I missing? Harr Hi try =SUMPRODUCT(--(B1:B1000="SMU"),--(C1:C1000<>"")) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany &qu...

Sum of every nth cell in a column
I am trying to figure out the formula for adding the nth cell in a given column. I am using the formula below to add every 4th cell but it is just one that I found on line. Can someone explain to me the components of this formula so I can adapt it? =SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0)) That is an array formula so it must be committed with Shift + Ctrl + <Enter>. here is how it works... Moving down through cells C2:C213 it looks at each 2 things. The value of the cell and the row that cell is on. If the row that cell is on is +1 is evenly divisible by 1 then ...

Office 2010
Is there a way to deploy a custom color in Office apps? I need to supply a specific rgb color code as a default option for business purposes during deployment - Thanks in advance neok wrote: > Is there a way to deploy a custom color in Office apps? > I need to supply a specific rgb color code as a default option for business > purposes during deployment - > > Thanks in advance Please post your query here: <http://social.answers.microsoft.com/Forums/en-US/officeinstall/threads?lc=1033&filter=answered> Microsoft is gradually moving all supports ...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...

How to generate a cell reference from multiple cells
For example, to acquire the cell reference 'c5' the C is generated by a formula in one cell, and the 5 is generated in a different cell Try this... A1 = C B1 = 5 =INDIRECT(A1&B1) Which evaluates to =$C$5 -- Biff Microsoft Excel MVP "Malters" <Malters@discussions.microsoft.com> wrote in message news:F8BDB88A-7371-4C02-8973-B7E8F3E5E50D@microsoft.com... > For example, to acquire the cell reference 'c5' the C is generated by a > formula in one cell, and the 5 is generated in a different cell ...

Force a new line in a cell with "wrap text" on
How can I put a newline command in a cell formula? I've tried using Alt-Enter, but it doesn't work. Why would you want to wrap a formula? Do you mean text, or an actual formula? "DRARetired" <draretired@sbcglobal.net> wrote in message news:edf201c43d16$e7f8e5c0$a001280a@phx.gbl... > How can I put a newline command in a cell formula? I've > tried using Alt-Enter, but it doesn't work. In a Worksheet use CHAR(10) in a macro use CHR(10) Alt+Enter automatically turns on Cell Wrap. This won't so you may have to format the cells yourself. Fo...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

Not matching transactions
I have it set to not automatically do anything. I've tried resetting the account quite a few times with the help of technicians, but it's too long and involved, and I'm tired of rebalancing the account every time. I have 3 accounts, 1 works without passport and does fine. One of the others I can manually download from the web site, and have no problems doing it like so and decided this is the best way, prior tries it wouldn't work with passport. But the other still insists they are new transactions, and I can't download from their site directly. They only allow...

Average only cells >0
Is there any way to create a formula that takes an average of all cells in range that are greater than the value of zero? If the value is zero, it should not be included in the average. Thank you! ...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Aam" <Aam@discussions.microsoft.com> wrote in message news:C51E8ED0-443D-4B35-B436-0A9EDB09B419@microsoft.com... > while impo...

Start a file at a defaulted worksheet/cell?
Howdy, Having a few users to a file, can I have the file open to a default worksheet/cell as I have directions I want them to read before playing... Regards, Kevin Kevin, use something like this, put in thisworkbook code Private Sub Workbook_Open() Sheets("Sheet2").Select Range("C3").Select End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "...

Manually matching transactions
Hi, I'm new to Money 2007 so maybe this is an easy question but I don't find an answer after searching help and recent posts. I have setup a checking account and a bill for Tuition. My transactions are automatically downloaded to my checking account. One of these transactions was my Tuition payment, but the bill still shows as being past due. Can't I simply mark that transaction as being a Tuition payment somehow? I'm sure I could record payment from the bill with the same info as that transaction has then delete the downloaded transaction that wasn't associated ...