Code Help

hi guys, i have a vb question.  i have the following code:

____________________________________________________
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("B6")) Is Nothing Then Exit Sub

If (Target.Value) = "Rental" Then
Range("F6").NumberFormat = "$#,##"
Else
Range("F6").NumberFormat = "0.0%"
End If

End Sub
------------------------------------------------------------------------------------

how do i make it so that if b6 changes, then f6 will change.  if b7
changes, then f7 will change.  if b8 changes, then f8 will change, and
so on...

any help would be greatly appreciated.  thanks!!
0
8/28/2003 5:29:16 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
477 Views

Similar Articles

[PageSpeed] 16

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("B6:b100")) Is Nothing Then Exit Sub
    If (Target.Value) = "Rental" Then
        Target.Offset(0, 4).NumberFormat = "$#,##"
    Else
        Target.Offset(0, 4).NumberFormat = "0.0%"
    End If
End Sub


-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2002 SP-2)
www.rondebruin.nl



"RealEstate" <RealEstate.svtny@excelforum.com> wrote in message news:RealEstate.svtny@excelforum.com...
> hi guys, i have a vb question.  i have the following code:
>
> ____________________________________________________
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> If Target.Cells.Count > 1 Then Exit Sub
> If Intersect(Target, Range("B6")) Is Nothing Then Exit Sub
>
> If (Target.Value) = "Rental" Then
> Range("F6").NumberFormat = "$#,##"
> Else
> Range("F6").NumberFormat = "0.0%"
> End If
>
> End Sub
> ------------------------------------------------------------------------------------
>
> how do i make it so that if b6 changes, then f6 will change.  if b7
> changes, then f7 will change.  if b8 changes, then f8 will change, and
> so on...
>
> any help would be greatly appreciated.  thanks!!


0
rondebruin (3789)
8/28/2003 5:38:07 PM
Reply:

Similar Artilces:

Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...

zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...

Pagis and Vista Not Compatible
OK, I admit I was stupid. But I needed a document that had been transferred from my old computer to my new one. It had been scanned using Pagis so I couldn't open it. I installed the Pagis disc without even thinking that it was old and not compatible. I have since removed the "workings" of Pagis but had to use Revo Uninstaller to do it because the MS Installation Wizard didn't recognize Pagis deatails. I'm still stuck with the Pagis icon on my desk top and can not remove it. There is no "delete" when right clicking on the icon. It opens to the Pa...

concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...

Please help with recipient policy query
I originally posted this on the 11th but got no reply so I'm trying again... I have OUs called "CompanyOUA" and "CompanyOUB". Under both of those is another OU called "Users". I want to be able to create a recipient policy for users in the "CompanyOUA" only (i.e. not affecting "CompanyOUB". I don't seem to be able to piece together an LDAP query that satisfies this, at least not through the Recipient Policy GUI. Can anyone help? Is there a program or something that I can use to help me with this and to test the results of an LDAP q...

Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...

16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...

Macro help #10
Hi! I have a table with customers that have made multiple purchases of different products. I would like to have a macro that creates a purchase number based on the number of times they have purchased individual items. For example, the first time they purchase Product A a 1 would go in this new field, the second time they purchase ProductA a 2 would go in this field. I understand this would work through a date loop but I am lost as to how to accomplish this. I am not really familiar with writing macro's! Another key thing is that in order for it to be a purchase it must not have been a sam...

area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...

Desperate need for help
I'm stumped. I have an EX55 server that seems infected with some sort of mass mailing Trojan but I just can't find it. Here are the symptoms. Soon after turning on the SMTP connecter my out bound queue fills with NDRs destine to what appears to be domains in Korea. It look as if the mass mailer is emailing into my server with bogus addresses and causing the NDRs to go out. After just a few minutes the outbound queue is full and any legitimate email won't go out. I've turned off all really options and this happens even when the server is NOT connected to the network/i...

Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...

exceL NOOB needs help w/sales tracking
I manage a nightclub and want to create a sales tracking chart pe server. This is easy if all servers occupied the same cells on ever worksheet. However, corporate insists that the daily sales report b designed around stations. Therefore, my servers rotate from station t station on a daily basis. I know i could always make a seperat worksheet designed around the servers and input the server totals second time onto that worksheet but i was hoping of finding someway o linking a second worksheet that totals up the sales per server as enter them into the daily sales report (the one designed ...

help
hi, I have a real problem - that is getting to me, ok here goes sheet 1 lists transactions by departments on sheet 2 I would like to be able to add up the transactions for each dept. The problem is that the dept is column D but the amount is Column I any ideas ? David A Pivot Table is what you want. You can use the data on sheet1 and put the results on sheet2 If you are not familiar with Pivot Tables, see: http://peltiertech.com/Excel/Pivots/pivotstart.htm -- Gary's Student "David Cleland" wrote: > hi, > > I have a real problem - that is getting to me,...

Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...

Change code with code?
Hi All..... I've got 31 Excel .xlsm files to modify the "Change Event" macro in. Is it possible to open and perform this with code, or must I do each one by hand? TIA Vaya con Dios, Chuck, CABGx3 If it is the same change in each sheet, it would probably be just as easy to go into the VBE, double click on the first sheet, make the correction, then copy that correction and double click the second sheet, delete and paste. Repeat the process 30 times. Takes less time than writing the code to do it. "CLR" <CLR@discussions.microsoft.com> wr...

Access 2003
I have a form that goes to a subform for searching. It works perfectly on my computer; however, when another user opens it and attempts to perform a search, they receive the Error Code 2455 message. When I debug, it gets stuck on "Me.book_subform.Form.Filter = strWhereSearch" and says that book_subform cannot be found. It isn't a rights issue, it isn't a mapping issue. What am I missing? check your naming. This is an issue that often confused. In this syntax: Me.book_subform.Form.Filter > = strWhereSearch book_subform is the name of the subform control on the main ...

Help Creating XML File from Spreadsheet
Hi, I did some research on the net on how to create an xml file from a spreadsheet. I think I followed the steps 1) Create an xsd (schema) file that defines or is the design for the relationships and format of the output data 2) Import the xsd (schema) into your excel file - it should appear in the XML source area (little window on the right) 3) 'Map' the elements in the schema to the columns in your spreadsheet - THEN - 'in theory' you should be able to export your spreadsheet as an xml file..... HOWEVER .... all I get is an xml file with the column headi...

Write a code by code
How can I write a code from procedure to some Workbook's ThisWorkbook? With procedure1 a make Workbook with a table. I need to write Auto_Close procedure to ThisWorkbook which makes some controls before I close it. Is it possible? <jenista.j@discussions.microsoft.com> wrote in message news:0be501c52fab$ba0b6830$a601280a@phx.gbl... > How can I write a code from procedure to some Workbook's > ThisWorkbook? With procedure1 a make Workbook with a table. > I need to write Auto_Close procedure to ThisWorkbook which > makes some controls before I close it. Is it possible? ...

Coding Duplicate Records
How can I code that a record is to be "K" kept or "D" deleted when compared to its next row? Example: A sorted file by Address BEFORE LastName Address Sales Date Keep/Delete 1. WASSEM 0N642 TITUS PL 67,500 6/25/2001 2. HART 0N655 E WEAVER CIR 70,000 3/19/2001 3. HART 0N655 E WEAVER CIR 40,000 3/19/2001 4. HART 0N655 WEAVER CIRCLE 33,700 6/19/1998 5. POYTH 0N662 W WEAVER CIR 294,690 6/21/2000 6. BLAND 0N670 GREEN PL ...

help with/for pivottable code
Hi everyone! I have code that takes a large workbook and divides it into a number of separate new workbooks based on data that is in column A. What i need is to create code that will create a pivot table in the new workbook. here is my code: Sub Regionalize() Dim wks As Worksheet Dim wksNew As Worksheet Dim wbk As Workbook Dim rng As Range Dim cell As Range Dim lRow As Long Dim sFileName As String Dim sFolder As String Dim sRegion As String Set wks = Sheets("region") Set rng = wks.Range("regiondata") ...

Rebar example code
Can anyone send me a rebar code example. There does not seem to be any examples other than Microsoft documentation. Theres nothing on CodeProject or else where. Many thanks in advance, Aaron On 8 Apr 2007 13:30:14 -0700, "AaronNGray" <aaronngray@gmail.com> wrote: >Can anyone send me a rebar code example. There does not seem to be any >examples other than Microsoft documentation. Theres nothing on >CodeProject or else where. > >Many thanks in advance, > >Aaron CodeProject has that... https://secure.codeproject.com/docking/#ReBars : http://www.codepro...

Newbie: Help me choose a book?
I was given a gift card for Christmas so I could buy myself a book on Access. While that's a very nice gesture, it really limits my choices, and excludes one or two that were recommended to me. Below are what seems to be the best choices within my budget for books that will take me from beginning to mid-level database construction and maintenance. I currently use Excel for almost everything, and am reasonably proficient in Word and Excel VBA. I can make a basic table, query, form, and report in Access. I currently have Access 2003, so that's what the following titles are limited t...

need help with Outlook/Excel interface
how can I copy multiple Excel files from Outlook (all at once) into Excel? Is there a way to do this? When I right click ONE file attached to the email, I can copy it, either using My Computer, or opening the directory within Excel. BUT, if I clik 2 or more, and try to copy & paste, it doesn't work. I have 16 files to copy. Can you help? I've been looking online using microsoft "Help" for over 30 minutes (not too helpful!). Simple searches do NOT yield a solution!! Any real help you can give me is appreciated. Thanks. Mark I have only been able to save them a...