$$Excel-Macros$$ Re: Please help me to solve this problem
Hi Serghei, Thanks, Macros are working properly. Best regards, Swapnil. On Fri, Jul 31, 2009 at 2:33 PM, Serghei Ovanesov wrote: > Hello Swapnil, > > > Find attached the same solution in Excel 2007. However you must insure you > have macro's security settings set changed to enable all macros. Visit the > http://office.microsoft.com/en-us/excel/HP100969191033.aspx link for > instructions. Do confirm if this macros work now. > > > Kind regards. > > > > Serghei. > > > -- > From: oserg...@hotmail.com > To: excel-macros@googlegroups.com > Subject: RE: $$Excel-Macros$$ Re: Please help me to solve this problem > Date: Thu, 30 Jul 2009 16:05:45 + > > Hi Swapnil, > > Furthermore to my previous email you can alternatively import at > Tools>Macro>Visual Basic Editor screen the file attached. That also includes > solution to your first step which has been omitted in previous version of > the code. > > > Regards. > > > Serghei > > ------------------ > From: oserg...@hotmail.com > To: excel-macros@googlegroups.com > Subject: RE: $$Excel-Macros$$ Re: Please help me to solve this problem > Date: Thu, 30 Jul 2009 14:58:28 + > > Hi Swapnil, > > I have attached a text document with the VBA macro code which addresses > your problem. All you need to do is copy+paste the code under > Tools>Macro>Visual Basic Editor in Sheet1. > > I have tested the same here and it seems to do what you need. I am using > Excel 2003. > > > Try entering the values for the mentioned scenarios and confirm if the same > is working. > > > Regards. > > > Serghei. > -- > From: swapnilp1...@gmail.com > Date: Thu, 30 Jul 2009 19:55:30 +0530 > Subject: $$Excel-Macros$$ Re: Please help me to solve this problem > > To: excel-macros@googlegroups.com > > Hi Serghei, > > Thanks for giving reply. > > I saw you excel, but it is not solving my problem. I am not able to find > macro written in your excel. > > I want to apply conditional formatting on Major suppliers columns "PP" and > "PE" i.e. column M and N > > Suppose If I insert supplier name in "PP" column (i.e. cell "M5"), then it > will check that whether the total consumption of pp products (i.e. B5:E5) is > > 0 and also it will check that import (i.e. "L5") is > 0 if both these > conditions are true then it will not give any error (that means I can insert > supplier name in that cell - "M5"). But if one of these conditions is false, > that cell (i.e. "M5") should get fill with red colour which will indicate > that user cannot insert supplier name if consumption or import is 0. > > I hope you got my point. Actually it is little difficult to explained. > > Thanks and best regards, > > Swapnil. > > On Thu, Jul 30, 2009 at 7:28 PM, Serghei Ovanesov wrote: > > Hi Swapnil, > > > I am not sure if this is what you wanted, but here it goes. Unfortunately I > was not able to replicate the same with conditional formatting, thus i had > to resort to VBA macro. > > If anyone is able to do the same with conditional formatting please do > post. > > Find the VBA solution attached and do let me know if this has achieved > your goal. > > > Kind regards. > > > Serghei. > > -- > From: swapnilp1...@gmail.com > Date: Thu, 30 Jul 2009 11:17:35 +0530 > Subject: $$Excel-Macros$$ Please help me to solve this problem > To: excel-macros@googlegroups.com > > Hi All, > > I want to do conditional formatting on selected cells based on values > inserted in other cells. > > I have explained the problem in attached excel. If you still have any query > let me know I will try to explain it again. > > Thanks and Best regards, > > Swapnil. > > > > > > -- > Windows Live Messenger: Happy 10-Year Anniversary—get free winks and > emoticons. Get Them Now <http://clk.atdmt.com/UKM/go/157562755/direct/01/> > -- > Share your memories online with anyone you want. Learn > more.<http://clk.atdmt.com/UKM/go/134665338/direct/01/> > -- > Windows Live Messenger: Happy 10-Year Anniversary—get free winks and > emoticons. Get Them Now <http://clk.atdmt.com/UKM/go/157562755/direct/01/> > > > --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads
$$Excel-Macros$$ Re: Please help me to solve this problem
Hi Serghei, Thanks for your help. It is working fine. Meanwhile I also got the solution. I have written a macro for my 1st query ie. for calculating % value automatically and for second query I am doing conditional formatting. Following is the formula for conditional formatting. =OR(AND(L5 = 0%, M5 <>"NA"), AND(SUM(B5:E5) > 0, L5> 0%, M5 = "NA"), AND(SUM(B5:E5) = 0, M5 <>"NA")) I am attaching excel with above solutions. See the excel and let me know if there is any other way to do this. I am calling macro on AutoOpen event (in case of first query). So this macro will not work if you have already open another excel file. Thanks and Best regards, Swapnil. On Thu, Jul 30, 2009 at 9:35 PM, Serghei Ovanesov wrote: > Hi Swapnil, > > Furthermore to my previous email you can alternatively import at > Tools>Macro>Visual Basic Editor screen the file attached. That also includes > solution to your first step which has been omitted in previous version of > the code. > > > Regards. > > > Serghei > > ------ > From: oserg...@hotmail.com > To: excel-macros@googlegroups.com > Subject: RE: $$Excel-Macros$$ Re: Please help me to solve this problem > Date: Thu, 30 Jul 2009 14:58:28 + > > Hi Swapnil, > > I have attached a text document with the VBA macro code which addresses > your problem. All you need to do is copy+paste the code under > Tools>Macro>Visual Basic Editor in Sheet1. > > I have tested the same here and it seems to do what you need. I am using > Excel 2003. > > > Try entering the values for the mentioned scenarios and confirm if the same > is working. > > > Regards. > > > Serghei. > -- > From: swapnilp1...@gmail.com > Date: Thu, 30 Jul 2009 19:55:30 +0530 > Subject: $$Excel-Macros$$ Re: Please help me to solve this problem > > To: excel-macros@googlegroups.com > > Hi Serghei, > > Thanks for giving reply. > > I saw you excel, but it is not solving my problem. I am not able to find > macro written in your excel. > > I want to apply conditional formatting on Major suppliers columns "PP" and > "PE" i.e. column M and N > > Suppose If I insert supplier name in "PP" column (i.e. cell "M5"), then it > will check that whether the total consumption of pp products (i.e. B5:E5) is > > 0 and also it will check that import (i.e. "L5") is > 0 if both these > conditions are true then it will not give any error (that means I can insert > supplier name in that cell - "M5"). But if one of these conditions is false, > that cell (i.e. "M5") should get fill with red colour which will indicate > that user cannot insert supplier name if consumption or import is 0. > > I hope you got my point. Actually it is little difficult to explained. > > Thanks and best regards, > > Swapnil. > > On Thu, Jul 30, 2009 at 7:28 PM, Serghei Ovanesov wrote: > > Hi Swapnil, > > > I am not sure if this is what you wanted, but here it goes. Unfortunately I > was not able to replicate the same with conditional formatting, thus i had > to resort to VBA macro. > > If anyone is able to do the same with conditional formatting please do > post. > > Find the VBA solution attached and do let me know if this has achieved > your goal. > > > Kind regards. > > > Serghei. > > -- > From: swapnilp1...@gmail.com > Date: Thu, 30 Jul 2009 11:17:35 +0530 > Subject: $$Excel-Macros$$ Please help me to solve this problem > To: excel-macros@googlegroups.com > > Hi All, > > I want to do conditional formatting on selected cells based on values > inserted in other cells. > > I have explained the problem in attached excel. If you still have any query > let me know I will try to explain it again. > > Thanks and Best regards, > > Swapnil. > > > > > > -- > Windows Live Messenger: Happy 10-Year Anniversary—get free winks and > emoticons. Get Them Now <http://clk.atdmt.com/UKM/go/157562755/direct/01/> > -- > Share your memories online with anyone you want. Learn > more.<http://clk.atdmt.com/UKM/go/134665338/direct/01/> > > > --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http
$$Excel-Macros$$ Re: Please help me to solve this problem
Hi Swapnil, Furthermore to my previous email you can alternatively import at Tools>Macro>Visual Basic Editor screen the file attached. That also includes solution to your first step which has been omitted in previous version of the code. Regards. Serghei From: oserg...@hotmail.com To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Re: Please help me to solve this problem Date: Thu, 30 Jul 2009 14:58:28 + Hi Swapnil, I have attached a text document with the VBA macro code which addresses your problem. All you need to do is copy+paste the code under Tools>Macro>Visual Basic Editor in Sheet1. I have tested the same here and it seems to do what you need. I am using Excel 2003. Try entering the values for the mentioned scenarios and confirm if the same is working. Regards. Serghei. From: swapnilp1...@gmail.com Date: Thu, 30 Jul 2009 19:55:30 +0530 Subject: $$Excel-Macros$$ Re: Please help me to solve this problem To: excel-macros@googlegroups.com Hi Serghei, Thanks for giving reply. I saw you excel, but it is not solving my problem. I am not able to find macro written in your excel. I want to apply conditional formatting on Major suppliers columns "PP" and "PE" i.e. column M and N Suppose If I insert supplier name in "PP" column (i.e. cell "M5"), then it will check that whether the total consumption of pp products (i.e. B5:E5) is > 0 and also it will check that import (i.e. "L5") is > 0 if both these conditions are true then it will not give any error (that means I can insert supplier name in that cell - "M5"). But if one of these conditions is false, that cell (i.e. "M5") should get fill with red colour which will indicate that user cannot insert supplier name if consumption or import is 0. I hope you got my point. Actually it is little difficult to explained. Thanks and best regards, Swapnil. On Thu, Jul 30, 2009 at 7:28 PM, Serghei Ovanesov wrote: Hi Swapnil, I am not sure if this is what you wanted, but here it goes. Unfortunately I was not able to replicate the same with conditional formatting, thus i had to resort to VBA macro. If anyone is able to do the same with conditional formatting please do post. Find the VBA solution attached and do let me know if this has achieved your goal. Kind regards. Serghei. From: swapnilp1...@gmail.com Date: Thu, 30 Jul 2009 11:17:35 +0530 Subject: $$Excel-Macros$$ Please help me to solve this problem To: excel-macros@googlegroups.com Hi All, I want to do conditional formatting on selected cells based on values inserted in other cells. I have explained the problem in attached excel. If you still have any query let me know I will try to explain it again. Thanks and Best regards, Swapnil. Windows Live Messenger: Happy 10-Year Anniversary—get free winks and emoticons. Get Them Now _ Share your memories online with anyone you want. http://clk.atdmt.com/UKM/go/134665338/direct/01/ --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 5,200 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~--- VERSION 1.0 CLASS BEGIN MultiUse = -1 'True END Attribute VB_Name = "Sheet1" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = False Attribute VB_PredeclaredId = True Attribute VB_Exposed = True Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False 'When Imported column is updated do the following validations If Target.Column = 12 Then If Target.Value > 0 Then Call CheckMajorSuppPP(Target) Call CheckMajorSuppPE(Target) ElseIf Target.Value = 0 Or Target.Value = "" Then If IsEmpty(Cells(Target.R
$$Excel-Macros$$ Re: Please help me to solve this problem
Hi Swapnil, I have attached a text document with the VBA macro code which addresses your problem. All you need to do is copy+paste the code under Tools>Macro>Visual Basic Editor in Sheet1. I have tested the same here and it seems to do what you need. I am using Excel 2003. Try entering the values for the mentioned scenarios and confirm if the same is working. Regards. Serghei. From: swapnilp1...@gmail.com Date: Thu, 30 Jul 2009 19:55:30 +0530 Subject: $$Excel-Macros$$ Re: Please help me to solve this problem To: excel-macros@googlegroups.com Hi Serghei, Thanks for giving reply. I saw you excel, but it is not solving my problem. I am not able to find macro written in your excel. I want to apply conditional formatting on Major suppliers columns "PP" and "PE" i.e. column M and N Suppose If I insert supplier name in "PP" column (i.e. cell "M5"), then it will check that whether the total consumption of pp products (i.e. B5:E5) is > 0 and also it will check that import (i.e. "L5") is > 0 if both these conditions are true then it will not give any error (that means I can insert supplier name in that cell - "M5"). But if one of these conditions is false, that cell (i.e. "M5") should get fill with red colour which will indicate that user cannot insert supplier name if consumption or import is 0. I hope you got my point. Actually it is little difficult to explained. Thanks and best regards, Swapnil. On Thu, Jul 30, 2009 at 7:28 PM, Serghei Ovanesov wrote: Hi Swapnil, I am not sure if this is what you wanted, but here it goes. Unfortunately I was not able to replicate the same with conditional formatting, thus i had to resort to VBA macro. If anyone is able to do the same with conditional formatting please do post. Find the VBA solution attached and do let me know if this has achieved your goal. Kind regards. Serghei. From: swapnilp1...@gmail.com Date: Thu, 30 Jul 2009 11:17:35 +0530 Subject: $$Excel-Macros$$ Please help me to solve this problem To: excel-macros@googlegroups.com Hi All, I want to do conditional formatting on selected cells based on values inserted in other cells. I have explained the problem in attached excel. If you still have any query let me know I will try to explain it again. Thanks and Best regards, Swapnil. _ Windows Live Messenger: Happy 10-Year Anniversary—get free winks and emoticons. http://clk.atdmt.com/UKM/go/157562755/direct/01/ --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 5,200 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~--- Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False 'When Imported column is updated do the following validations If Target.Column = 12 Then If Target.Value > 0 Then Call CheckMajorSuppPP(Target) Call CheckMajorSuppPE(Target) ElseIf Target.Value = 0 Or Target.Value = "" Then If IsEmpty(Cells(Target.Row, Target.Column + 1).Value) = False Then Call CheckConsumptionPP(Target) End If If IsEmpty(Cells(Target.Row, Target.Column + 2).Value) = False Then Call CheckConsumptionPE(Target) End If End If End If 'Check if the user has rectified previously incorrect entries Call CheckMajorSuppliesChanges(Target) 'Check the sum of consumtion and check PP/PE entries Call CheckConsumptionPP(Target) Call CheckConsumptionPE(Target) Application.EnableEvents = True Exit Sub End Sub Sub CheckConsumptionPE(ByVal Target) 'Check Consumption of PE If (Cells(Target.Row, 6).Value + Cells(Target.Row, 7
$$Excel-Macros$$ Re: Please help me to solve this problem
Hi Serghei, Thanks for giving reply. I saw you excel, but it is not solving my problem. I am not able to find macro written in your excel. I want to apply conditional formatting on Major suppliers columns "PP" and "PE" i.e. column M and N Suppose If I insert supplier name in "PP" column (i.e. cell "M5"), then it will check that whether the total consumption of pp products (i.e. B5:E5) is > 0 and also it will check that import (i.e. "L5") is > 0 if both these conditions are true then it will not give any error (that means I can insert supplier name in that cell - "M5"). But if one of these conditions is false, that cell (i.e. "M5") should get fill with red colour which will indicate that user cannot insert supplier name if consumption or import is 0. I hope you got my point. Actually it is little difficult to explained. Thanks and best regards, Swapnil. On Thu, Jul 30, 2009 at 7:28 PM, Serghei Ovanesov wrote: > Hi Swapnil, > > > I am not sure if this is what you wanted, but here it goes. Unfortunately I > was not able to replicate the same with conditional formatting, thus i had > to resort to VBA macro. > > If anyone is able to do the same with conditional formatting please do > post. > > Find the VBA solution attached and do let me know if this has achieved > your goal. > > > Kind regards. > > > Serghei. > > -- > From: swapnilp1...@gmail.com > Date: Thu, 30 Jul 2009 11:17:35 +0530 > Subject: $$Excel-Macros$$ Please help me to solve this problem > To: excel-macros@googlegroups.com > > Hi All, > > I want to do conditional formatting on selected cells based on values > inserted in other cells. > > I have explained the problem in attached excel. If you still have any query > let me know I will try to explain it again. > > Thanks and Best regards, > > Swapnil. > > > > --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 5,200 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
