| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
two worksheet linking
hi
experts I am working on 2 worksheet. In worksheet 1, there is a column called "Customer". In worksheet 2, all the data related to customer is stored. I have classified customer into 3 different types. Class A (Blue), Class B(yellow), Class C(Pink) No. of customers in every class is different and may get added on in future. Class A Class B Class C (Blue) (yellow) (pink) Cus1 Cus1 Cus1 Cus2 Cus 2 Cus2 Cus3 Cus 3 Cus3 Cus4 Cus 4 Cus4 Cus5 Cus5 Cus5 Cus6 Cus6 Cus6 Cus7 Cus7 Cus8 I have developed a MS Access Query. The data in Customer column in Worksheet 1 gets filled in automatically as soon as I run a macro associated with the query. What I am after is as soon as data gets filled in, colour for that customer should change based on the class that customer belongs to. Now I have shown only 8, 7 and 6 no. of customer for class A, B and C respectively. In future, as customer gets added on, the cell colour for that class of customer should change automatically. I am basically after linking 2 worksheet to get this working. Any suggestion will be greatly appreciated. Thanking in anticipation |
|
#2
|
|||
|
|||
|
|
|
#4
|
|||
|
|||
|
Hi Minal,
Your query was not very clear to me. But let me try to understand what you want. You have sheet 1, where you will get some data as cus1, cus2, etc. What is the format, and how is this arranged. The table you gave in your first post, is it what is in sheet1, or its in a different sheet. How does one know what is the color for each class. Have you a text entry saying this is pink, or have you colored the cell with pink color (the second one is more easy). If you answer these queries, I could give you a way to proceed. Mangesh "MINAL ZUNKE" wrote in message ... Hi Gary I didn't understand you rsolution. Can you plz make it more detail. I am after changing colour as well. The problem I am having is coz I have VB script. I will explain you. Column F heading is Status, as soon as job on the section finishes, somebody puts C in column F. The entire row gets cut and paste onto sheet3. At the moment I am using column from A to W. If you can suggest some solution for cutting From column A to W, that will solve my problem for time being. Thnx for your suggestion "Gary L Brown" wrote: Change the structure so col A has Class and col B has Customer. That will drastically reduce your issues. HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "MINAL ZUNKE" wrote: hi experts I am working on 2 worksheet. In worksheet 1, there is a column called "Customer". In worksheet 2, all the data related to customer is stored. I have classified customer into 3 different types. Class A (Blue), Class B(yellow), Class C(Pink) No. of customers in every class is different and may get added on in future. Class A Class B Class C (Blue) (yellow) (pink) Cus1 Cus1 Cus1 Cus2 Cus 2 Cus2 Cus3 Cus 3 Cus3 Cus4 Cus 4 Cus4 Cus5 Cus5 Cus5 Cus6 Cus6 Cus6 Cus7 Cus7 Cus8 I have developed a MS Access Query. The data in Customer column in Worksheet 1 gets filled in automatically as soon as I run a macro associated with the query. What I am after is as soon as data gets filled in, colour for that customer should change based on the class that customer belongs to. Now I have shown only 8, 7 and 6 no. of customer for class A, B and C respectively. In future, as customer gets added on, the cell colour for that class of customer should change automatically. I am basically after linking 2 worksheet to get this working. Any suggestion will be greatly appreciated. Thanking in anticipation |
|
#5
|
|||
|
|||
|
Hi
Mangesh Answer to your question all the customer name will be stored in one column (i.e column B) in sheet 1 only. The table will be in sheet 2. Table which I say will be in sheet 2, it was in sheet 1 before. It was in column AD, AE & AF. Everything was working fine. I used conditional formatting under column heading B. What I have done before Condition 1 Formula is =OR(B4=$AD$3,B4=$AD$4,B4=$AD$5,B4=$AD$6,B4=$AD$7,B 4=$AD$8,B4=$AD$9) Condition 2 Formula is =OR(B4=$AE$3,B4=$AE$4,B4=$AE$5,B4=$AE$6,B4=$AE$7,B 4=$AE$8,B4=$AE$9) Condition 3 formula is =OR(B4=$AF$3,B4=$AF$4,B4=$AF$5,B4=$AF$6,B4=$AF$7,B 4=$AF$8) Each formula represents one class of customer. Now I want to just change my formulas . If you see 1st formula B4=$AD$3,B4=$AD$4,................................ .. Formula 1 change requirement $AD$3 should be replaced by the sheet 2 column A row 3 cell reference $AD$4 sholud be replaced by the sheet 2 column A row 4 cell reference $AD$5 should be replaced by the sheet 2 column B row 5 cell reference. Formula 2 B4=$AE$3,B4=$AE$4, ..................... formula 2 change requirement $AE$3 should be replaced by sheet 2 column B row 3 cell reference $AE$4 should be replaced by sheet 2 column B row 4 cell reference $AE$5 should be replaced by sheet 2 column B row 5 cell reference And this way same for formula 3 as well. I hope for the correct solution!! Thanking you in anticipation!! |
|
#6
|
|||
|
|||
|
I MANAGED TO SOLVE THE PROBLEM ON OWN.
THNX TO THOSE WHOSE TOOK TIME TO HELP ME OUT.!!!!!!!!! CHEERS "MINAL ZUNKE" wrote: hi experts I am working on 2 worksheet. In worksheet 1, there is a column called "Customer". In worksheet 2, all the data related to customer is stored. I have classified customer into 3 different types. Class A (Blue), Class B(yellow), Class C(Pink) No. of customers in every class is different and may get added on in future. Class A Class B Class C (Blue) (yellow) (pink) Cus1 Cus1 Cus1 Cus2 Cus 2 Cus2 Cus3 Cus 3 Cus3 Cus4 Cus 4 Cus4 Cus5 Cus5 Cus5 Cus6 Cus6 Cus6 Cus7 Cus7 Cus8 I have developed a MS Access Query. The data in Customer column in Worksheet 1 gets filled in automatically as soon as I run a macro associated with the query. What I am after is as soon as data gets filled in, colour for that customer should change based on the class that customer belongs to. Now I have shown only 8, 7 and 6 no. of customer for class A, B and C respectively. In future, as customer gets added on, the cell colour for that class of customer should change automatically. I am basically after linking 2 worksheet to get this working. Any suggestion will be greatly appreciated. Thanking in anticipation |
|
#7
|
|||
|
|||
|
HI EXPERTS
I still could not solve the problem as it says """YOU CAN NOT USE REFERENCE TO OTHER WORKSHEET OR WORKBOOK FOR CONDITIONAL FORMATTING CRITERION""". I think it's beyond my capabilities now hope to get good working suggestion!! "MINAL ZUNKE" wrote: I MANAGED TO SOLVE THE PROBLEM ON OWN. THNX TO THOSE WHOSE TOOK TIME TO HELP ME OUT.!!!!!!!!! CHEERS "MINAL ZUNKE" wrote: hi experts I am working on 2 worksheet. In worksheet 1, there is a column called "Customer". In worksheet 2, all the data related to customer is stored. I have classified customer into 3 different types. Class A (Blue), Class B(yellow), Class C(Pink) No. of customers in every class is different and may get added on in future. Class A Class B Class C (Blue) (yellow) (pink) Cus1 Cus1 Cus1 Cus2 Cus 2 Cus2 Cus3 Cus 3 Cus3 Cus4 Cus 4 Cus4 Cus5 Cus5 Cus5 Cus6 Cus6 Cus6 Cus7 Cus7 Cus8 I have developed a MS Access Query. The data in Customer column in Worksheet 1 gets filled in automatically as soon as I run a macro associated with the query. What I am after is as soon as data gets filled in, colour for that customer should change based on the class that customer belongs to. Now I have shown only 8, 7 and 6 no. of customer for class A, B and C respectively. In future, as customer gets added on, the cell colour for that class of customer should change automatically. I am basically after linking 2 worksheet to get this working. Any suggestion will be greatly appreciated. Thanking in anticipation |
|
#8
|
|||
|
|||
|
Since I already have a small code, I might as well post it.
Use this in the sheet1's event module: Private Sub Worksheet_Change(ByVal Target As Range) Set sht2 = Worksheets("Sheet2") If Target.Column = 2 Then Set rngA = sht2.Range(sht2.Range("A3"), sht2.Range("A3").End(xlDown)) Set rngB = sht2.Range(sht2.Range("B3"), sht2.Range("B3").End(xlDown)) Set rngC = sht2.Range(sht2.Range("C3"), sht2.Range("C3").End(xlDown)) Set v = rngA.Find(Target.Value, LookIn:=xlValues) If v Is Nothing Then Set v = rngB.Find(Target.Value, LookIn:=xlValues) If v Is Nothing Then Set v = rngC.Find(Target.Value, LookIn:=xlValues) If v Is Nothing Then Target.Interior.ColorIndex = 0 Else Target.Interior.ColorIndex = rngB(1).Offset(-1, 0).Interior.ColorIndex End If Else Target.Interior.ColorIndex = rngB(1).Offset(-1, 0).Interior.ColorIndex End If Else Target.Interior.ColorIndex = rngA(1).Offset(-1, 0).Interior.ColorIndex End If End If End Sub With your table in sheet2, and the row 2 cells will have a background color as indicated in the text above Mangesh "MINAL ZUNKE" wrote in message ... I MANAGED TO SOLVE THE PROBLEM ON OWN. THNX TO THOSE WHOSE TOOK TIME TO HELP ME OUT.!!!!!!!!! CHEERS "MINAL ZUNKE" wrote: hi experts I am working on 2 worksheet. In worksheet 1, there is a column called "Customer". In worksheet 2, all the data related to customer is stored. I have classified customer into 3 different types. Class A (Blue), Class B(yellow), Class C(Pink) No. of customers in every class is different and may get added on in future. Class A Class B Class C (Blue) (yellow) (pink) Cus1 Cus1 Cus1 Cus2 Cus 2 Cus2 Cus3 Cus 3 Cus3 Cus4 Cus 4 Cus4 Cus5 Cus5 Cus5 Cus6 Cus6 Cus6 Cus7 Cus7 Cus8 I have developed a MS Access Query. The data in Customer column in Worksheet 1 gets filled in automatically as soon as I run a macro associated with the query. What I am after is as soon as data gets filled in, colour for that customer should change based on the class that customer belongs to. Now I have shown only 8, 7 and 6 no. of customer for class A, B and C respectively. In future, as customer gets added on, the cell colour for that class of customer should change automatically. I am basically after linking 2 worksheet to get this working. Any suggestion will be greatly appreciated. Thanking in anticipation |
|
#9
|
|||
|
|||
|
Hi Minal,
precisely, I thought so, and I was wondering how you solved it with conditional formatting. Check my post where I have given a VBA solution. Mangesh "MINAL ZUNKE" wrote in message ... HI EXPERTS I still could not solve the problem as it says """YOU CAN NOT USE REFERENCE TO OTHER WORKSHEET OR WORKBOOK FOR CONDITIONAL FORMATTING CRITERION""". I think it's beyond my capabilities now hope to get good working suggestion!! "MINAL ZUNKE" wrote: I MANAGED TO SOLVE THE PROBLEM ON OWN. THNX TO THOSE WHOSE TOOK TIME TO HELP ME OUT.!!!!!!!!! CHEERS "MINAL ZUNKE" wrote: hi experts I am working on 2 worksheet. In worksheet 1, there is a column called "Customer". In worksheet 2, all the data related to customer is stored. I have classified customer into 3 different types. Class A (Blue), Class B(yellow), Class C(Pink) No. of customers in every class is different and may get added on in future. Class A Class B Class C (Blue) (yellow) (pink) Cus1 Cus1 Cus1 Cus2 Cus 2 Cus2 Cus3 Cus 3 Cus3 Cus4 Cus 4 Cus4 Cus5 Cus5 Cus5 Cus6 Cus6 Cus6 Cus7 Cus7 Cus8 I have developed a MS Access Query. The data in Customer column in Worksheet 1 gets filled in automatically as soon as I run a macro associated with the query. What I am after is as soon as data gets filled in, colour for that customer should change based on the class that customer belongs to. Now I have shown only 8, 7 and 6 no. of customer for class A, B and C respectively. In future, as customer gets added on, the cell colour for that class of customer should change automatically. I am basically after linking 2 worksheet to get this working. Any suggestion will be greatly appreciated. Thanking in anticipation |
|
#10
|
|||
|
|||
|
Hi Mangesh
Thank you for VB code. I have copied all the coding in sheet1, but nothing seems to happen. Can you plz suggest me some tricks so that it should work fine. How do I set the colour for column A,B & C in sheet 2. I tried to do that but didn't get through it. I want to set the colour format for each class. Say for class A: cyan blue, CLass B: yellow, Class C: Magenta Thanking you in anticipation!! "Mangesh Yadav" wrote: Since I already have a small code, I might as well post it. Use this in the sheet1's event module: Private Sub Worksheet_Change(ByVal Target As Range) Set sht2 = Worksheets("Sheet2") If Target.Column = 2 Then Set rngA = sht2.Range(sht2.Range("A3"), sht2.Range("A3").End(xlDown)) Set rngB = sht2.Range(sht2.Range("B3"), sht2.Range("B3").End(xlDown)) Set rngC = sht2.Range(sht2.Range("C3"), sht2.Range("C3").End(xlDown)) Set v = rngA.Find(Target.Value, LookIn:=xlValues) If v Is Nothing Then Set v = rngB.Find(Target.Value, LookIn:=xlValues) If v Is Nothing Then Set v = rngC.Find(Target.Value, LookIn:=xlValues) If v Is Nothing Then Target.Interior.ColorIndex = 0 Else Target.Interior.ColorIndex = rngB(1).Offset(-1, 0).Interior.ColorIndex End If Else Target.Interior.ColorIndex = rngB(1).Offset(-1, 0).Interior.ColorIndex End If Else Target.Interior.ColorIndex = rngA(1).Offset(-1, 0).Interior.ColorIndex End If End If End Sub With your table in sheet2, and the row 2 cells will have a background color as indicated in the text above Mangesh |
|
#11
|
|||
|
|||
|
Hi Minal,
The following code was written with the example you gave. The only thing you need to do is: In sheet2, cells A2, B2, and C2 should be colored (the backgroud should be colored as per your requirements). Mangesh "MINAL ZUNKE" wrote in message ... Hi Mangesh Thank you for VB code. I have copied all the coding in sheet1, but nothing seems to happen. Can you plz suggest me some tricks so that it should work fine. How do I set the colour for column A,B & C in sheet 2. I tried to do that but didn't get through it. I want to set the colour format for each class. Say for class A: cyan blue, CLass B: yellow, Class C: Magenta Thanking you in anticipation!! "Mangesh Yadav" wrote: Since I already have a small code, I might as well post it. Use this in the sheet1's event module: Private Sub Worksheet_Change(ByVal Target As Range) Set sht2 = Worksheets("Sheet2") If Target.Column = 2 Then Set rngA = sht2.Range(sht2.Range("A3"), sht2.Range("A3").End(xlDown)) Set rngB = sht2.Range(sht2.Range("B3"), sht2.Range("B3").End(xlDown)) Set rngC = sht2.Range(sht2.Range("C3"), sht2.Range("C3").End(xlDown)) Set v = rngA.Find(Target.Value, LookIn:=xlValues) If v Is Nothing Then Set v = rngB.Find(Target.Value, LookIn:=xlValues) If v Is Nothing Then Set v = rngC.Find(Target.Value, LookIn:=xlValues) If v Is Nothing Then Target.Interior.ColorIndex = 0 Else Target.Interior.ColorIndex = rngB(1).Offset(-1, 0).Interior.ColorIndex End If Else Target.Interior.ColorIndex = rngB(1).Offset(-1, 0).Interior.ColorIndex End If Else Target.Interior.ColorIndex = rngA(1).Offset(-1, 0).Interior.ColorIndex End If End If End Sub With your table in sheet2, and the row 2 cells will have a background color as indicated in the text above Mangesh |
|
#12
|
|||
|
|||
|
Hi Mangesh
Thnx for your suggestion!!! I appreciate you help, Sorry but mothing seems to happen again. As you said the code was written a/c to my example, I would like to clarify few more bits. I have set background colour in sheet 2 cell A3, B3 & C3 (and not whole column) as per my choice. I already have set up conditional formatting in sheet 1 in other columns. I have written VB script in sheet 1. I am using column A to column W in sheet 1. Data in sheet 1 from column A to column E gets copied over automatically. I have developed MS Access query to get the data. After I analyse executed query with excel, all the data in executed query gets copied over to another excel file(say A). I have recorded macro to copy data from ( A) excel file into main excel file sheet 1. Data in column B in main excel file(where all the customer name gets listed) also gets copied over. As I have said before as soon as I run macro, data gets copied over, from column A to column E, column B should change colour depending on the class. I think this might help you a bit. If you need more help from me, I am happy to answer your query. Thanking you in anticipation!!! Minal "Mangesh Yadav" wrote: Hi Minal, The following code was written with the example you gave. The only thing you need to do is: In sheet2, cells A2, B2, and C2 should be colored (the backgroud should be colored as per your requirements). Mangesh "MINAL ZUNKE" wrote in message ... Hi Mangesh |
|
#13
|
|||
|
|||
|
First thing before I read your mail completely, set the background color for
cells A2, B2 and C2 and not row 3. Mangesh "MINAL ZUNKE" wrote in message ... Hi Mangesh Thnx for your suggestion!!! I appreciate you help, Sorry but mothing seems to happen again. As you said the code was written a/c to my example, I would like to clarify few more bits. I have set background colour in sheet 2 cell A3, B3 & C3 (and not whole column) as per my choice. I already have set up conditional formatting in sheet 1 in other columns. I have written VB script in sheet 1. I am using column A to column W in sheet 1. Data in sheet 1 from column A to column E gets copied over automatically. I have developed MS Access query to get the data. After I analyse executed query with excel, all the data in executed query gets copied over to another excel file(say A). I have recorded macro to copy data from ( A) excel file into main excel file sheet 1. Data in column B in main excel file(where all the customer name gets listed) also gets copied over. As I have said before as soon as I run macro, data gets copied over, from column A to column E, column B should change colour depending on the class. I think this might help you a bit. If you need more help from me, I am happy to answer your query. Thanking you in anticipation!!! Minal "Mangesh Yadav" wrote: Hi Minal, The following code was written with the example you gave. The only thing you need to do is: In sheet2, cells A2, B2, and C2 should be colored (the backgroud should be colored as per your requirements). Mangesh "MINAL ZUNKE" wrote in message ... Hi Mangesh |
|
#14
|
|||
|
|||
|
Hi mangesh
I have done that , the day you gave me solution. I have done that this way Select Cell A2 in sheet 2, click right hand mouse button, select patterns, select colour, click ok . Done that for cell B2, C2 as well. The background colour for the cell A2 changes as per settings but this information is not getting passed onto sheet 1. Thnx for ur help! "Mangesh Yadav" wrote: First thing before I read your mail completely, set the background color for cells A2, B2 and C2 and not row 3. Mangesh "MINAL ZUNKE" wrote in message ... Hi Mangesh Thnx for your suggestion!!! I appreciate you help, Sorry but mothing seems to happen again. As you said the code was written a/c to my example, I would like to clarify few more bits. I have set background colour in sheet 2 cell A3, B3 & C3 (and not whole column) as per my choice. I already have set up conditional formatting in sheet 1 in other columns. I have written VB script in sheet 1. I am using column A to column W in sheet 1. Data in sheet 1 from column A to column E gets copied over automatically. I have developed MS Access query to get the data. After I analyse executed query with excel, all the data in executed query gets copied over to another excel file(say A). I have recorded macro to copy data from ( A) excel file into main excel file sheet 1. Data in column B in main excel file(where all the customer name gets listed) also gets copied over. As I have said before as soon as I run macro, data gets copied over, from column A to column E, column B should change colour depending on the class. I think this might help you a bit. If you need more help from me, I am happy to answer your query. Thanking you in anticipation!!! Minal "Mangesh Yadav" wrote: Hi Minal, The following code was written with the example you gave. The only thing you need to do is: In sheet2, cells A2, B2, and C2 should be colored (the backgroud should be colored as per your requirements). Mangesh |
|
#15
|
|||
|
|||
|
Hi again
Sorry but it is not working. If I check code no error in that, that background colour information is not getting passed on. Can't figure out any reason for that. Thanking you in anticiapation! "MINAL ZUNKE" wrote: Hi mangesh I have done that , the day you gave me solution. I have done that this way Select Cell A2 in sheet 2, click right hand mouse button, select patterns, select colour, click ok . Done that for cell B2, C2 as well. The background colour for the cell A2 changes as per settings but this information is not getting passed onto sheet 1. Thnx for ur help! "Mangesh Yadav" wrote: First thing before I read your mail completely, set the background color for cells A2, B2 and C2 and not row 3. Mangesh "MINAL ZUNKE" wrote in message ... Hi Mangesh Thnx for your suggestion!!! I appreciate you help, Sorry but mothing seems to happen again. As you said the code was written a/c to my example, I would like to clarify few more bits. I have set background colour in sheet 2 cell A3, B3 & C3 (and not whole column) as per my choice. I already have set up conditional formatting in sheet 1 in other columns. I have written VB script in sheet 1. I am using column A to column W in sheet 1. Data in sheet 1 from column A to column E gets copied over automatically. I have developed MS Access query to get the data. After I analyse executed query with excel, all the data in executed query gets copied over to another excel file(say A). I have recorded macro to copy data from ( A) excel file into main excel file sheet 1. Data in column B in main excel file(where all the customer name gets listed) also gets copied over. As I have said before as soon as I run macro, data gets copied over, from column A to column E, column B should change colour depending on the class. I think this might help you a bit. If you need more help from me, I am happy to answer your query. Thanking you in anticipation!!! Minal "Mangesh Yadav" wrote: Hi Minal, The following code was written with the example you gave. The only thing you need to do is: In sheet2, cells A2, B2, and C2 should be colored (the backgroud should be colored as per your requirements). Mangesh |
|
#16
|
|||
|
|||
|
If you don't mind sharing your excel sheet, why don't you mail me your excel
sheet with a little explanation. Maybe something like I just have to press a button to run your macro, and a little write-up on what should happen after pressing this button. You can write to me at my gmail id. And remove NOSPAM. Mangesh "MINAL ZUNKE" wrote in message ... Hi again Sorry but it is not working. If I check code no error in that, that background colour information is not getting passed on. Can't figure out any reason for that. Thanking you in anticiapation! "MINAL ZUNKE" wrote: Hi mangesh I have done that , the day you gave me solution. I have done that this way Select Cell A2 in sheet 2, click right hand mouse button, select patterns, select colour, click ok . Done that for cell B2, C2 as well. The background colour for the cell A2 changes as per settings but this information is not getting passed onto sheet 1. Thnx for ur help! "Mangesh Yadav" wrote: First thing before I read your mail completely, set the background color for cells A2, B2 and C2 and not row 3. Mangesh "MINAL ZUNKE" wrote in message ... Hi Mangesh Thnx for your suggestion!!! I appreciate you help, Sorry but mothing seems to happen again. As you said the code was written a/c to my example, I would like to clarify few more bits. I have set background colour in sheet 2 cell A3, B3 & C3 (and not whole column) as per my choice. I already have set up conditional formatting in sheet 1 in other columns. I have written VB script in sheet 1. I am using column A to column W in sheet 1. Data in sheet 1 from column A to column E gets copied over automatically. I have developed MS Access query to get the data. After I analyse executed query with excel, all the data in executed query gets copied over to another excel file(say A). I have recorded macro to copy data from ( A) excel file into main excel file sheet 1. Data in column B in main excel file(where all the customer name gets listed) also gets copied over. As I have said before as soon as I run macro, data gets copied over, from column A to column E, column B should change colour depending on the class. I think this might help you a bit. If you need more help from me, I am happy to answer your query. Thanking you in anticipation!!! Minal "Mangesh Yadav" wrote: Hi Minal, The following code was written with the example you gave. The only thing you need to do is: In sheet2, cells A2, B2, and C2 should be colored (the backgroud should be colored as per your requirements). Mangesh |
|
#17
|
|||
|
|||
|
hi Mangesh
If you can give me your email-id that will be really helpful. I can forward my excel sheet with explanation in it. Thnx "Mangesh Yadav" wrote: If you don't mind sharing your excel sheet, why don't you mail me your excel sheet with a little explanation. Maybe something like I just have to press a button to run your macro, and a little write-up on what should happen after pressing this button. You can write to me at my gmail id. And remove NOSPAM. Mangesh "MINAL ZUNKE" wrote in message ... Hi again Sorry but it is not working. If I check code no error in that, that background colour information is not getting passed on. Can't figure out any reason for that. Thanking you in anticiapation! "MINAL ZUNKE" wrote: Hi mangesh I have done that , the day you gave me solution. I have done that this way Select Cell A2 in sheet 2, click right hand mouse button, select patterns, select colour, click ok . Done that for cell B2, C2 as well. The background colour for the cell A2 changes as per settings but this information is not getting passed onto sheet 1. Thnx for ur help! "Mangesh Yadav" wrote: First thing before I read your mail completely, set the background color for cells A2, B2 and C2 and not row 3. Mangesh "MINAL ZUNKE" wrote in message ... Hi Mangesh Thnx for your suggestion!!! I appreciate you help, Sorry but mothing seems to happen again. As you said the code was written a/c to my example, I would like to clarify few more bits. I have set background colour in sheet 2 cell A3, B3 & C3 (and not whole column) as per my choice. I already have set up conditional formatting in sheet 1 in other columns. I have written VB script in sheet 1. I am using column A to column W in sheet 1. Data in sheet 1 from column A to column E gets copied over automatically. I have developed MS Access query to get the data. After I analyse executed query with excel, all the data in executed query gets copied over to another excel file(say A). I have recorded macro to copy data from ( A) excel file into main excel file sheet 1. Data in column B in main excel file(where all the customer name gets listed) also gets copied over. As I have said before as soon as I run macro, data gets copied over, from column A to column E, column B should change colour depending on the class. I think this might help you a bit. If you need more help from me, I am happy to answer your query. Thanking you in anticipation!!! Minal "Mangesh Yadav" wrote: Hi Minal, The following code was written with the example you gave. The only thing you need to do is: In sheet2, cells A2, B2, and C2 should be colored (the backgroud should be colored as per your requirements). Mangesh |
|
#18
|
|||
|
|||
|
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel worksheet linking | Excel Worksheet Functions | |||
| Linking multiple data into one worksheet. | Excel Worksheet Functions | |||
| Linking cells in a worksheet to other worksheets in a workbook | Excel Discussion (Misc queries) | |||
| Worksheet linking | Excel Worksheet Functions | |||
| Linking items GREATER THAN O on another worksheet in the same Work | Excel Discussion (Misc queries) | |||
Linear Mode
