Re: $$Excel-Macros$$ date format changes

[NOORAIN ANSARI]

Dear Amit,

Please try this formula.

=TEXT(A5,"dd-mmm-yy")

in vba, You can use

Function Date_format(rng As Range)
Date_format = VBA.Format(rng, "dd-mmm-yy")
End Function


-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

On Wed, Nov 16, 2011 at 12:32 PM, Amit Desai (MERU)  wrote:

>  Dear All,
>
> ** **
>
> How can i change 01/25/2011 to 25-Jan-2011 with a formula.. i have tried
> changing it with text to column (date option)... also done format cell
> option..but could not work...
>
> ** **
>
> Regards,
>
> *Amit Desai*
>
> --
> Disclaimer: This message and its attachments contain confidential
> information and may also contain legally privileged information. This
> message is intended solely for the named addressee. If you are not the
> addressee indicated in this message (or authorized to receive for
> addressee), you may not copy or deliver any part of this message or its
> attachments to anyone or use any part of this message or its attachments.
> Rather, you should permanently delete this message and its attachments (and
> all copies) from your system and kindly notify the sender by reply e-mail.
> Any content of this message and its attachments that does not relate to the
> official business of Meru Cab Company Pvt. Ltd. must be taken not to have
> been sent or endorsed by any of them. Email communications are not private
> and no warranty is made that e-mail communications are timely, secure or
> free from computer virus or other defect.
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Pivot report with button

[chhajersandeep]

Thanks Noorain. 

Sandeep Chhajer.
Sent on my BlackBerry® from Vodafone

-Original Message-
From: NOORAIN ANSARI 
Sender: excel-macros@googlegroups.com
Date: Wed, 16 Nov 2011 11:58:18 
To: 
Reply-To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Pivot report with button

Dear Sandeep,

Please find attached sheet with Pivot Table+Button.
Hope it will help to u.


-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

On Wed, Nov 16, 2011 at 11:40 AM,  wrote:

> Dear all,
>
> I have to make many pivot report from same data...like one is LR wise...
> Other is Destination wise...Truck wise etc.
>
> What I want to assign a button for every report. Like if I click on
> button1 it will give me LR wise report if I click button 2 it will show me
> as Destination wise and so on
>
> I also don't want any where in the column name " sum of " would come.
>
> Can I do so with assigning macro.
>
>
> Thanks & Regards,
>
> CA Sandeep Chhajer.
> Sent on my BlackBerryŽ from Vodafone
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ date format changes

[Amit Desai (MERU)]

Dear All,

How can i change 01/25/2011 to 25-Jan-2011 with a formula.. i have tried 
changing it with text to column (date option)... also done format cell 
option..but could not work...

Regards,
Amit Desai


Disclaimer: This message and its attachments contain confidential information 
and may also contain legally privileged information. This message is intended 
solely for the named addressee. If you are not the addressee indicated in this 
message (or authorized to receive for addressee), you may not copy or deliver 
any part of this message or its attachments to anyone or use any part of this 
message or its attachments. Rather, you should permanently delete this message 
and its attachments (and all copies) from your system and kindly notify the 
sender by reply e-mail. Any content of this message and its attachments that 
does not relate to the official business of Meru Cab Company Pvt. Ltd. must be 
taken not to have been sent or endorsed by any of them. Email communications 
are not private and no warranty is made that e-mail communications are timely, 
secure or free from computer virus or other defect.

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Pivot report with button

[chhajersandeep]

Dear all,

I have to make many pivot report from same data...like one is LR wise...
Other is Destination wise...Truck wise etc.

What I want to assign a button for every report. Like if I click on button1 it 
will give me LR wise report if I click button 2 it will show me as Destination 
wise and so on

I also don't want any where in the column name " sum of " would come.

Can I do so with assigning macro.


Thanks & Regards,

CA Sandeep Chhajer.
Sent on my BlackBerryŽ from Vodafone

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Email Macro required for mail Boday

[maulik desai]

Hi Deepak sir, I have pasted the required format in Sheet 2 & yes i am
using Outlook 2007 to send mails...

On Mon, Nov 14, 2011 at 10:37 AM, Deepak Pal Singh <
deepaktheind...@gmail.com> wrote:

> Can you provide the format of your data.. Also want to know do you outlook
> to send emails..??
>
>
>
> On Sun, Nov 13, 2011 at 10:30 PM, maulik desai wrote:
>
>> Hi Team,
>>
>> I am having 1 excel sheet in that i have to prepare Chennel wise detail
>> report on a every week & i have to send that information to respective
>> chennel heads wia email ,
>> Manually i need to do filter the data base on chennal name & then
>> copy data from sheet & need to paste on mail body then i have to send the
>> data to that chennel head based on chennel name.
>>
>> My problem is i have to do same excerside many times as chennel name are
>> approxly 80
>>
>> Request you to kindly provide me the macro code witch can directly send
>> the email based on chennel name (mail body format given in attched
>> file ,sheet 2)
>> Thanks in advance.
>> --
>> Thanks & Regards,
>> Maulik Desai
>> 9967363926
>>
>> --
>> FORUM RULES (934+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Thanks & Regards,
Maulik Desai
9967363926

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Re: Email Macro required for mail Boday

[maulik desai]

Hi deepu,

Thanks you so much for your reply

I have tried to paste the code in module but it not work properly request
you to kindly provide me the excel sheet with your macro code but before
you send me the file required small changes do not use K & M column for
Chennel name & email address that is given just for information my actual
database is from A to G request you to kindly change the macro code
accordingly & send it back for the other checks also if you want u can
create the summary sheet with the help of macro & paste it on sheet 2 &
also for name u can use Column H for the proper output.1 more thing i want
to tell you that a have to send mail with our official signature so if u
can apply macro logic for the same also it would be very great ful to u.

Request you to kindly make the changes & resend it for the final check

Thanks in Advance & thanks for your time & support.

On Mon, Nov 14, 2011 at 4:10 PM, deepu  wrote:

> Hi Maulik,
>
> Below is the code that you're looking for...
> Insert a module in your workbook and paste the following code and run
> it...
>
>
> ---
> Dim MyArray() As Variant, ListArray() As Variant
> Dim R As Long, C As Integer, LastRow As Long, i As Long
> Dim OutObj As Object, EmailObj As Object, EmailBody As String, RecAdd
> As String
> Sub PrepareData()
> 'Assigning values to MyArray
> LastRow = Sheet1.Range("A6").End(xlUp).Row
> MyArray = Sheet1.Range("A7:G" & LastRow).Value
>
> 'Assigning values to ListArray
> LastRow = Sheet1.Range("K6").End(xlUp).Row
> ListArray = Sheet1.Range("K7:M" & LastRow).Value
>
> For i = LBound(ListArray) To UBound(ListArray)
>RecAdd = ListArray(i, 3)
>EmailBody = "Hi " &
> Application.WorksheetFunction.Proper(ListArray(i, 2)) & ", Align=center>FYI..." & _
>"" & _
>" Color=#FF>Customer Name" & _
>" Color=#FF>Location" & _
>" Color=#FF>Date" & _
>" Color=#FF>Site" & _
>" Color=#FF>Status" & _
>""
>
>For R = LBound(MyArray) To UBound(MyArray)
>If ListArray(i, 1) = MyArray(R, 6) Then
>For C = 1 To 5
>EmailBody = EmailBody & "" & _
>"" & MyArray(R, 1) & "" & _
>"" & MyArray(R, 2) & "" & _
>"" & MyArray(R, 3) & "" & _
>"" & MyArray(R, 4) & "" & _
>"" & MyArray(R, 5) & "" & _
>""
>Next C
>End If
>Next R
>Call SendEmail(RecAdd, EmailBody)
> Next i
> End Sub
>
> Sub SendEmail(ReceiptAdd As String, EBody As String)
> Set OutObj = CreateObject("Outlook.Application.12")
> Set EmailObj = OutObj.CreateItem(0)
>
> EmailBody = EmailBody & "" & _
> "RegardsXYZ"
>
> With EmailObj
>.To = ReceiptAdd
>.Subject = "Your Channels Data"
>.HTMLBody = EmailBody
>.Send
> End With
>
> End Sub
>
> ---
>
> Hope you'd like it.. Kindly contact to me if you face any problem...
>
> Regards
> Deepak
>
> On Nov 13, 10:00 pm, maulik desai  wrote:
> > Hi Team,
> >
> > I am having 1 excel sheet in that i have to prepare Chennel wise detail
> > report on a every week & i have to send that information to respective
> > chennel heads wia email ,
> > Manually i need to do filter the data base on chennal name & then copy
> data
> > from sheet & need to paste on mail body then i have to send the data to
> > that chennel head based on chennel name.
> >
> > My problem is i have to do same excerside many times as chennel name are
> > approxly 80
> >
> > Request you to kindly provide me the macro code witch can directly send
> the
> > email based on chennel name (mail body format given in attched file
> ,sheet
> > 2)
> > Thanks in advance.
> > --
> > Thanks & Regards,
> > Maulik Desai
> > 9967363926
> >
> >  Customer Info.xls
> > 33KViewDownload
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 

Re: $$Excel-Macros$$ Email Macro required for mail Boday

[maulik desai]

Thanks you so much sam for your prompt reply...

Just want some modification & changes in your code

1.) Macro code should not pick the chennel name from K Column ( i have just
created table from K to M for Understanding perpose),My actual data is from
Column A to Column G. And Acual chennel name is not based on Nos like
chennel 1 & 2 (it has it actual name) request you to keep chennel name
column as free text (not text or word specific (i can use any name in that).

2.) Email Column should be changed from M To G .Becasue actual data is very
huge & its changes on a weekly basis so it is diff for me to create summary
sheet (request you to kindly add the macro code to create summry sheet on
Sheet2 if required (for Peson name u can use column H, so range will be
from A to H )

3.) Currently after running the code, in mail body bottom table line is
not reflecting becasue of that it not look good.

4.) I want macro should also add the exsiting mail signature of mine before
mail sent to respective person.

Thanks you once again for your time, request you to kindly make the
required changes.


On Mon, Nov 14, 2011 at 10:56 PM, Sam Mathai Chacko wrote:

> Here's a macro for that. Not 100% checked.
>
> Regards
>
> Sam Mathai Chacko
>
> On Mon, Nov 14, 2011 at 7:07 AM, Deepak Pal Singh <
> deepaktheind...@gmail.com> wrote:
>
>> Can you provide the format of your data.. Also want to know do you
>> outlook to send emails..??
>>
>>
>>
>> On Sun, Nov 13, 2011 at 10:30 PM, maulik desai wrote:
>>
>>> Hi Team,
>>>
>>> I am having 1 excel sheet in that i have to prepare Chennel wise detail
>>> report on a every week & i have to send that information to respective
>>> chennel heads wia email ,
>>> Manually i need to do filter the data base on chennal name & then
>>> copy data from sheet & need to paste on mail body then i have to send the
>>> data to that chennel head based on chennel name.
>>>
>>> My problem is i have to do same excerside many times as chennel name are
>>> approxly 80
>>>
>>> Request you to kindly provide me the macro code witch can directly send
>>> the email based on chennel name (mail body format given in attched
>>> file ,sheet 2)
>>> Thanks in advance.
>>> --
>>> Thanks & Regards,
>>> Maulik Desai
>>> 9967363926
>>>
>>> --
>>> FORUM RULES (934+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> --
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>
>> --
>> FORUM RULES (934+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> Sam Mathai Chacko
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send

$$Excel-Macros$$ How do i print all files [any file type (.pdf,.doc,.tiff)] in a folder using vba macros

[ameya]

Hello,

Could you please guide me on how do i print all files [any file type
(.pdf,.doc,.tiff)] in a folder using vba macros.
The folder path is pre-defined.

Thanks

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

[Sam Mathai Chacko]

Totally buy that Don.

Cheers,
Sam

On Wed, Nov 16, 2011 at 12:50 AM, dguillett1  wrote:

>   Sam, I am one of those that does NOT like a lot of CF or even a lot of
> data validation. So, why not take a lot of the work away from the user and
> not clutter up the file with cf and dv. Let me know what you think of this
> idea to just double click col A>select number to fill in Text and color row.
>
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> Boolean)
> If Target.Count > 1 Or Target.Column <> 1 Then Exit Sub
> Dim mn As Double
> Dim x As String
> Dim y As Long
>
> Dim msg As String
> msg = "Enter 1 for One"
> msg = msg & vbLf & "Enter 2 for Two"
> msg = msg & vbLf & "Enter 3 for Three"
> msg = msg & vbLf & "Enter 4 for Four"
> msg = msg & vbLf & "Enter 5 for Five"
>
> mn = Application.InputBox(Prompt:=msg, Title:="Enter a Number",
> Type:=1)
> If mn < 1 Or mn > 5 Then
>MsgBox "Invalid entry", vbExclamation
>Exit Sub
> End If
>
> Select Case mn
> Case 1:  x = "One": y = 12
> Case 2: x = "Two": y = 22
> Case 3: x = "Three": y = 6
> Case 4: x = "Four": y = 5
> Case 5: x = "Five": y = 4
> Case Else
> End Select
>
> Target = x
> Rows(Target.Row).Interior.ColorIndex = y
> End Sub
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>  *From:* Sam Mathai Chacko 
> *Sent:* Tuesday, November 15, 2011 11:38 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Conditional formatting of entire row
> using VBA
>
> FJO, if you are using Excel 2003 or less, then I am afraid the solution I
> posted will not be very useful, as it requires more than 3 conditionals,
> which only Excel 2007+ supports.
>
> So in that case, Don's VBA code should suit you fine. But if you are in
> Excel 2007 or 2010, you could use multiple conditionals for formatting
> (which is what I'd prefer, it also helps in reducing VBA)
>
> Regards,
> Sam
>
> On Tue, Nov 15, 2011 at 10:35 PM, dguillett1  wrote:
>
>>   Sam, I’m sorry.I opened in xl2003duh
>>
>> Don Guillett
>> SalesAid Software
>> dguille...@gmail.com
>>
>>  *From:* Sam Mathai Chacko 
>> *Sent:* Tuesday, November 15, 2011 10:51 AM
>>  *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Conditional formatting of entire row
>> using VBA
>>
>>  Yes Don. I was just showing how it is done for one conditional. I had
>> it done for One to Five in the attachment I had posted.
>>
>> Regards,
>>
>> Sam
>>
>> On Tue, Nov 15, 2011 at 10:07 PM, dguillett1 wrote:
>>
>>>   Sam,
>>> > "one"
>>> > "two"
>>>
>>> Don Guillett
>>> SalesAid Software
>>> dguille...@gmail.com
>>>
>>>  *From:* Sam Mathai Chacko 
>>> *Sent:* Tuesday, November 15, 2011 10:21 AM
>>> *To:* excel-macros@googlegroups.com
>>> *Subject:* Re: $$Excel-Macros$$ Conditional formatting of entire row
>>> using VBA
>>>
>>>  Here's how you do it. Select your entire row. Assuming your top left
>>> cell in the entire selection is A1 (will be any one cell in the first
>>> column depending on where you started your selection)
>>>
>>> Go to conditional format, and in the options 'Use formula to determine
>>> which cells to format', write =$A1="One"
>>>
>>> and then apply the required format you need.
>>>
>>> Check the attachment.
>>>
>>> Regards,
>>>
>>> Sam Mathai Chacko
>>>
>>> On Tue, Nov 15, 2011 at 7:19 PM, FJO  wrote:
>>>
 Hi

 Can anyone help me with the VBA code for conditional formatting in
 Excel.

 I have af worksheet of 1000 rows with 5 colums. I have data in colum
 B;E and would like Excel to format the rows in a colour depending of
 what I write in colum A.

 I have 5 different text imput I can chose for each row, lets call
 them
 "one"
 "two"
 "Three"
 "Four"
 "Five"

 ...just to keep this example as simple as possible.

 If I write "one" in A2, I would like Excel to color Row 2 in one
 color, if I write "Two" then another color and so on.

 I found out how to color the cell I changed, but I would like to have
 the whole row colored.

 I hope someone can send me an example.

 --
 FORUM RULES (934+ members already BANNED for violation)

 1) Use concise, accurate thread titles. Poor thread titles, like Please
 Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
 will not get quick attention or may not be answered.

 2) Don't post a question in the thread of another member.

 3) Don't post questions regarding breaking or bypassing any security
 measure.

 4) Acknowledge the responses you receive, good or bad.

 5)  Cross-promotion of, or links to, forums competitive to this forum
 in signatures are prohibited.

 NOTE  : Don't ever post personal or confidential data in a workbook.
 Forum owners and members are not responsible for any loss.


 -

Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

[dguillett1]

Sam, I am one of those that does NOT like a lot of CF or even a lot of data 
validation. So, why not take a lot of the work away from the user and not 
clutter up the file with cf and dv. Let me know what you think of this idea to 
just double click col A>select number to fill in Text and color row.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As 
Boolean)
If Target.Count > 1 Or Target.Column <> 1 Then Exit Sub
Dim mn As Double
Dim x As String
Dim y As Long

Dim msg As String
msg = "Enter 1 for One"
msg = msg & vbLf & "Enter 2 for Two"
msg = msg & vbLf & "Enter 3 for Three"
msg = msg & vbLf & "Enter 4 for Four"
msg = msg & vbLf & "Enter 5 for Five"

mn = Application.InputBox(Prompt:=msg, Title:="Enter a Number", Type:=1)
If mn < 1 Or mn > 5 Then
   MsgBox "Invalid entry", vbExclamation
   Exit Sub
End If

Select Case mn
Case 1:  x = "One": y = 12
Case 2: x = "Two": y = 22
Case 3: x = "Three": y = 6
Case 4: x = "Four": y = 5
Case 5: x = "Five": y = 4
Case Else
End Select

Target = x
Rows(Target.Row).Interior.ColorIndex = y
End Sub

Don Guillett
SalesAid Software
dguille...@gmail.com

From: Sam Mathai Chacko 
Sent: Tuesday, November 15, 2011 11:38 AM
To: excel-macros@googlegroups.com 
Subject: Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

FJO, if you are using Excel 2003 or less, then I am afraid the solution I 
posted will not be very useful, as it requires more than 3 conditionals, which 
only Excel 2007+ supports.

So in that case, Don's VBA code should suit you fine. But if you are in Excel 
2007 or 2010, you could use multiple conditionals for formatting (which is what 
I'd prefer, it also helps in reducing VBA)

Regards,
Sam


On Tue, Nov 15, 2011 at 10:35 PM, dguillett1  wrote:

  Sam, I’m sorry.I opened in xl2003duh

  Don Guillett
  SalesAid Software
  dguille...@gmail.com

  From: Sam Mathai Chacko 
  Sent: Tuesday, November 15, 2011 10:51 AM
  To: excel-macros@googlegroups.com 
  Subject: Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

  Yes Don. I was just showing how it is done for one conditional. I had it done 
for One to Five in the attachment I had posted.

  Regards,

  Sam


  On Tue, Nov 15, 2011 at 10:07 PM, dguillett1  wrote:

Sam,
> "one"
> "two"

Don Guillett
SalesAid Software
dguille...@gmail.com

From: Sam Mathai Chacko 
Sent: Tuesday, November 15, 2011 10:21 AM
To: excel-macros@googlegroups.com 
Subject: Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

Here's how you do it. Select your entire row. Assuming your top left cell 
in the entire selection is A1 (will be any one cell in the first column 
depending on where you started your selection)

Go to conditional format, and in the options 'Use formula to determine 
which cells to format', write =$A1="One"

and then apply the required format you need.

Check the attachment.

Regards,

Sam Mathai Chacko


On Tue, Nov 15, 2011 at 7:19 PM, FJO  wrote:

  Hi

  Can anyone help me with the VBA code for conditional formatting in
  Excel.

  I have af worksheet of 1000 rows with 5 colums. I have data in colum
  B;E and would like Excel to format the rows in a colour depending of
  what I write in colum A.

  I have 5 different text imput I can chose for each row, lets call
  them
  "one"
  "two"
  "Three"
  "Four"
  "Five"

  ...just to keep this example as simple as possible.

  If I write "one" in A2, I would like Excel to color Row 2 in one
  color, if I write "Two" then another color and so on.

  I found out how to color the cell I changed, but I would like to have
  the whole row colored.

  I hope someone can send me an example.

  --
  FORUM RULES (934+ members already BANNED for violation)

  1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.

  2) Don't post a question in the thread of another member.

  3) Don't post questions regarding breaking or bypassing any security 
measure.

  4) Acknowledge the responses you receive, good or bad.

  5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited.

  NOTE  : Don't ever post personal or confidential data in a workbook. 
Forum owners and members are not responsible for any loss.

  
--
  To post to this group, send email to excel-macros@googlegroups.com




-- 
Sam Mathai Chacko
-- 
FORUM RULES (934+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, li

Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

[FJO]

Hi Sam

I am aware of the possibilities in office 2007 and later. But this
sheet will be used by multiple users, someone still in office 2003.
Therefore I will save it in the old office format.

I knew where to put Don's code, and that worked, but I then wanted to
change the words the conditional formatting depended on, but I was not
able to do that in VBA.

Thanks for your suggestions. I have tested those in office 2010 and
they work great. I would have chosen that solution if I knew that none
of the users were in office 2003.

Br Finn

On 15 Nov., 19:48, Sam Mathai Chacko  wrote:
> Don's code should be pasted in the sheet code module of the corresponding
> sheet that you are working on. Please check. By the way, you wouldn't need
> VBA for the desired feature if you are using Excel 2007 or greater.
>
> Sam
>
>
>
>
>
> On Wed, Nov 16, 2011 at 12:03 AM, FJO  wrote:
> > Hi Don Guillett
>
> > Thanks for your answer. It works perfect in the example. I then hoped
> > I could change a few things and the it would still work, but I can
> > not, and I can not see why.
>
> > Instead of writing "One", "Two" and so on in Colum A, I would like to
> > change this for different status, examples could be "Not started",
> > Awating info", "Finished"
>
> > I have tried to put these different status texts into the code, but
> > then it does not work. I am new to VBA, so I am sorry if this is
> > obvious to others.
>
> > Br. Finn
>
> > On 15 Nov., 17:21, "dguillett1"  wrote:
> > > Right click sheet tab>view code>insert this
>
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.Count > 1 Or Target.Column <> 1 Then Exit Sub
> > > Select Case LCase(Target)
> > > Case Is = "one": x = 1
> > > Case Is = "one": x = 1
> > > Case Is = "two": x = 6
> > > Case Is = "three": x = 3
> > > Case Is = "four": x = 4
> > > Case Else
> > > End Select
> > > Rows(Target.Row).Interior.ColorIndex = x
> > > End Sub
>
> > > Don Guillett
> > > SalesAid Software
> > > dguille...@gmail.com
>
> > > -Original Message-
> > > From: FJO
> > > Sent: Tuesday, November 15, 2011 7:49 AM
> > > To: MS EXCEL AND VBA MACROS
> > > Subject: $$Excel-Macros$$ Conditional formatting of entire row using VBA
>
> > > Hi
>
> > > Can anyone help me with the VBA code for conditional formatting in
> > > Excel.
>
> > > I have af worksheet of 1000 rows with 5 colums. I have data in colum
> > > B;E and would like Excel to format the rows in a colour depending of
> > > what I write in colum A.
>
> > > I have 5 different text imput I can chose for each row, lets call
> > > them
> > > "one"
> > > "two"
> > > "Three"
> > > "Four"
> > > "Five"
>
> > > ...just to keep this example as simple as possible.
>
> > > If I write "one" in A2, I would like Excel to color Row 2 in one
> > > color, if I write "Two" then another color and so on.
>
> > > I found out how to color the cell I changed, but I would like to have
> > > the whole row colored.
>
> > > I hope someone can send me an example.
>
> > > --
> > > FORUM RULES (934+ members already BANNED for violation)
>
> > > 1) Use concise, accurate thread titles. Poor thread titles, like Please
> > > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> > will
> > > not get quick attention or may not be answered.
>
> > > 2) Don't post a question in the thread of another member.
>
> > > 3) Don't post questions regarding breaking or bypassing any security
> > > measure.
>
> > > 4) Acknowledge the responses you receive, good or bad.
>
> > > 5)  Cross-promotion of, or links to, forums competitive to this forum in
> > > signatures are prohibited.
>
> > > NOTE  : Don't ever post personal or confidential data in a workbook.
> > Forum
> > > owners and members are not responsible for any loss.
>
> > ---­­---
> > > To post to this group, send email to excel-macros@googlegroups.com
>
> > --
> > FORUM RULES (934+ members already BANNED for violation)
>
> > 1) Use concise, accurate thread titles. Poor thread titles, like Please
> > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> > will not get quick attention or may not be answered.
>
> > 2) Don't post a question in the thread of another member.
>
> > 3) Don't post questions regarding breaking or bypassing any security
> > measure.
>
> > 4) Acknowledge the responses you receive, good or bad.
>
> > 5)  Cross-promotion of, or links to, forums competitive to this forum in
> > signatures are prohibited.
>
> > NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> > owners and members are not responsible for any loss.
>
> > ---­---
> > To post to this group, send email to excel-macros@googlegroups.com
>
> --
> Sam Mathai Chacko

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. P

Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

[Sam Mathai Chacko]

Don's code should be pasted in the sheet code module of the corresponding
sheet that you are working on. Please check. By the way, you wouldn't need
VBA for the desired feature if you are using Excel 2007 or greater.

Sam

On Wed, Nov 16, 2011 at 12:03 AM, FJO  wrote:

> Hi Don Guillett
>
> Thanks for your answer. It works perfect in the example. I then hoped
> I could change a few things and the it would still work, but I can
> not, and I can not see why.
>
> Instead of writing "One", "Two" and so on in Colum A, I would like to
> change this for different status, examples could be "Not started",
> Awating info", "Finished"
>
> I have tried to put these different status texts into the code, but
> then it does not work. I am new to VBA, so I am sorry if this is
> obvious to others.
>
> Br. Finn
>
> On 15 Nov., 17:21, "dguillett1"  wrote:
> > Right click sheet tab>view code>insert this
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Count > 1 Or Target.Column <> 1 Then Exit Sub
> > Select Case LCase(Target)
> > Case Is = "one": x = 1
> > Case Is = "one": x = 1
> > Case Is = "two": x = 6
> > Case Is = "three": x = 3
> > Case Is = "four": x = 4
> > Case Else
> > End Select
> > Rows(Target.Row).Interior.ColorIndex = x
> > End Sub
> >
> > Don Guillett
> > SalesAid Software
> > dguille...@gmail.com
> >
> >
> >
> > -Original Message-
> > From: FJO
> > Sent: Tuesday, November 15, 2011 7:49 AM
> > To: MS EXCEL AND VBA MACROS
> > Subject: $$Excel-Macros$$ Conditional formatting of entire row using VBA
> >
> > Hi
> >
> > Can anyone help me with the VBA code for conditional formatting in
> > Excel.
> >
> > I have af worksheet of 1000 rows with 5 colums. I have data in colum
> > B;E and would like Excel to format the rows in a colour depending of
> > what I write in colum A.
> >
> > I have 5 different text imput I can chose for each row, lets call
> > them
> > "one"
> > "two"
> > "Three"
> > "Four"
> > "Five"
> >
> > ...just to keep this example as simple as possible.
> >
> > If I write "one" in A2, I would like Excel to color Row 2 in one
> > color, if I write "Two" then another color and so on.
> >
> > I found out how to color the cell I changed, but I would like to have
> > the whole row colored.
> >
> > I hope someone can send me an example.
> >
> > --
> > FORUM RULES (934+ members already BANNED for violation)
> >
> > 1) Use concise, accurate thread titles. Poor thread titles, like Please
> > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will
> > not get quick attention or may not be answered.
> >
> > 2) Don't post a question in the thread of another member.
> >
> > 3) Don't post questions regarding breaking or bypassing any security
> > measure.
> >
> > 4) Acknowledge the responses you receive, good or bad.
> >
> > 5)  Cross-promotion of, or links to, forums competitive to this forum in
> > signatures are prohibited.
> >
> > NOTE  : Don't ever post personal or confidential data in a workbook.
> Forum
> > owners and members are not responsible for any loss.
> >
> >
> ---­---
> > To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Sam Mathai Chacko

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to 

Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

[FJO]

Hi Don Guillett

Thanks for your answer. It works perfect in the example. I then hoped
I could change a few things and the it would still work, but I can
not, and I can not see why.

Instead of writing "One", "Two" and so on in Colum A, I would like to
change this for different status, examples could be "Not started",
Awating info", "Finished"

I have tried to put these different status texts into the code, but
then it does not work. I am new to VBA, so I am sorry if this is
obvious to others.

Br. Finn

On 15 Nov., 17:21, "dguillett1"  wrote:
> Right click sheet tab>view code>insert this
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count > 1 Or Target.Column <> 1 Then Exit Sub
> Select Case LCase(Target)
> Case Is = "one": x = 1
> Case Is = "one": x = 1
> Case Is = "two": x = 6
> Case Is = "three": x = 3
> Case Is = "four": x = 4
> Case Else
> End Select
> Rows(Target.Row).Interior.ColorIndex = x
> End Sub
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>
>
> -Original Message-
> From: FJO
> Sent: Tuesday, November 15, 2011 7:49 AM
> To: MS EXCEL AND VBA MACROS
> Subject: $$Excel-Macros$$ Conditional formatting of entire row using VBA
>
> Hi
>
> Can anyone help me with the VBA code for conditional formatting in
> Excel.
>
> I have af worksheet of 1000 rows with 5 colums. I have data in colum
> B;E and would like Excel to format the rows in a colour depending of
> what I write in colum A.
>
> I have 5 different text imput I can chose for each row, lets call
> them
> "one"
> "two"
> "Three"
> "Four"
> "Five"
>
> ...just to keep this example as simple as possible.
>
> If I write "one" in A2, I would like Excel to color Row 2 in one
> color, if I write "Two" then another color and so on.
>
> I found out how to color the cell I changed, but I would like to have
> the whole row colored.
>
> I hope someone can send me an example.
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
> ---­---
> To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Query on dd-mm & mm-yy format

[Sam Mathai Chacko]

=IF(IFERROR(DATE(YEAR(G23),MONTH(G23),DAY(G23))=G23,0),MONTH(G23)&"-"&IF(DAY(G23)=1,0,DAY(G23)),G23)

Regards,

Sam Mathai Chacko

On Tue, Nov 15, 2011 at 3:28 PM, Suyog Kulkarni wrote:

> Hello Noorain,
>
> Thanks for quick response but I've added two problems in same excel.
> Please review & advice.
>
> Suyog
>
>
>
> On Tue, Nov 15, 2011 at 3:14 PM, NOORAIN ANSARI 
> wrote:
>
>> Dear Suyog,
>>
>> Please see attached sheet.
>>
>>
>>
>> --
>> Thanks & regards,
>> Noorain Ansari
>>  *http://excelmacroworld.blogspot.com/*
>> *http://noorain-ansari.blogspot.com/*
>>
>> On Tue, Nov 15, 2011 at 10:32 AM, Suyog Kulkarni <
>> suyog.kulka...@yahoo.com> wrote:
>>
>>> Hello Friends,
>>>
>>> Find attached excel file for ref.
>>>
>>> I'm using one software which list out required material for structure in
>>> excel. Output of that excel file converts some length of "Ft-In" format in
>>> "mm-dd-yy or mm-yy" format.
>>> Ex: For Length 10-11, excel shows Nov-11 & For length 11-10, excel shows
>>> 10/11/11.
>>>
>>> Somehow I need to keep all length either in text format or in Ft-inch
>>> format. I'm not importing file into excel so I can't fix the "text type"
>>> for particular column.
>>>
>>> Manual edition will be tedious as there are more than 75 pages (Each
>>> have more than 1000 rows), If anybody can suggest any easy way to save time.
>>>
>>> Thanks & regards,
>>> --
>>> *Suyog Kulkarni*
>>> Mailto:-suyog.kulka...@yahoo.com
>>>suyog.kulka...@hotmail.com
>>> Ph:-( +91 ) 9890123539
>>>
>>> --
>>> FORUM RULES (934+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> --
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>
>>
>>
>>  --
>> FORUM RULES (934+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> Suyog Kulkarni
> Mailto:-suyog.kulka...@yahoo.com
>suyog.kulka...@hotmail.com
> Ph:-( +91 ) 9890123539
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Sam Mathai Chacko

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums com

Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

[Sam Mathai Chacko]

FJO, if you are using Excel 2003 or less, then I am afraid the solution I
posted will not be very useful, as it requires more than 3 conditionals,
which only Excel 2007+ supports.

So in that case, Don's VBA code should suit you fine. But if you are in
Excel 2007 or 2010, you could use multiple conditionals for formatting
(which is what I'd prefer, it also helps in reducing VBA)

Regards,
Sam

On Tue, Nov 15, 2011 at 10:35 PM, dguillett1  wrote:

>   Sam, I’m sorry.I opened in xl2003duh
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>  *From:* Sam Mathai Chacko 
> *Sent:* Tuesday, November 15, 2011 10:51 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Conditional formatting of entire row
> using VBA
>
> Yes Don. I was just showing how it is done for one conditional. I had it
> done for One to Five in the attachment I had posted.
>
> Regards,
>
> Sam
>
> On Tue, Nov 15, 2011 at 10:07 PM, dguillett1  wrote:
>
>>   Sam,
>> > "one"
>> > "two"
>>
>> Don Guillett
>> SalesAid Software
>> dguille...@gmail.com
>>
>>  *From:* Sam Mathai Chacko 
>> *Sent:* Tuesday, November 15, 2011 10:21 AM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Conditional formatting of entire row
>> using VBA
>>
>>  Here's how you do it. Select your entire row. Assuming your top left
>> cell in the entire selection is A1 (will be any one cell in the first
>> column depending on where you started your selection)
>>
>> Go to conditional format, and in the options 'Use formula to determine
>> which cells to format', write =$A1="One"
>>
>> and then apply the required format you need.
>>
>> Check the attachment.
>>
>> Regards,
>>
>> Sam Mathai Chacko
>>
>> On Tue, Nov 15, 2011 at 7:19 PM, FJO  wrote:
>>
>>> Hi
>>>
>>> Can anyone help me with the VBA code for conditional formatting in
>>> Excel.
>>>
>>> I have af worksheet of 1000 rows with 5 colums. I have data in colum
>>> B;E and would like Excel to format the rows in a colour depending of
>>> what I write in colum A.
>>>
>>> I have 5 different text imput I can chose for each row, lets call
>>> them
>>> "one"
>>> "two"
>>> "Three"
>>> "Four"
>>> "Five"
>>>
>>> ...just to keep this example as simple as possible.
>>>
>>> If I write "one" in A2, I would like Excel to color Row 2 in one
>>> color, if I write "Two" then another color and so on.
>>>
>>> I found out how to color the cell I changed, but I would like to have
>>> the whole row colored.
>>>
>>> I hope someone can send me an example.
>>>
>>> --
>>> FORUM RULES (934+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5)  Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE  : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> --
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>
>>
>>
>> --
>> Sam Mathai Chacko
>> --
>> FORUM RULES (934+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>  --
>> FORUM RULES (934+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>

Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

[dguillett1]

Sam, I’m sorry.I opened in xl2003duh

Don Guillett
SalesAid Software
dguille...@gmail.com

From: Sam Mathai Chacko 
Sent: Tuesday, November 15, 2011 10:51 AM
To: excel-macros@googlegroups.com 
Subject: Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

Yes Don. I was just showing how it is done for one conditional. I had it done 
for One to Five in the attachment I had posted.

Regards,

Sam


On Tue, Nov 15, 2011 at 10:07 PM, dguillett1  wrote:

  Sam,
  > "one"
  > "two"

  Don Guillett
  SalesAid Software
  dguille...@gmail.com

  From: Sam Mathai Chacko 
  Sent: Tuesday, November 15, 2011 10:21 AM
  To: excel-macros@googlegroups.com 
  Subject: Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

  Here's how you do it. Select your entire row. Assuming your top left cell in 
the entire selection is A1 (will be any one cell in the first column depending 
on where you started your selection)

  Go to conditional format, and in the options 'Use formula to determine which 
cells to format', write =$A1="One"

  and then apply the required format you need.

  Check the attachment.

  Regards,

  Sam Mathai Chacko


  On Tue, Nov 15, 2011 at 7:19 PM, FJO  wrote:

Hi

Can anyone help me with the VBA code for conditional formatting in
Excel.

I have af worksheet of 1000 rows with 5 colums. I have data in colum
B;E and would like Excel to format the rows in a colour depending of
what I write in colum A.

I have 5 different text imput I can chose for each row, lets call
them
"one"
"two"
"Three"
"Four"
"Five"

...just to keep this example as simple as possible.

If I write "one" in A2, I would like Excel to color Row 2 in one
color, if I write "Two" then another color and so on.

I found out how to color the cell I changed, but I would like to have
the whole row colored.

I hope someone can send me an example.

--
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security 
measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited.

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.


--
To post to this group, send email to excel-macros@googlegroups.com




  -- 
  Sam Mathai Chacko
  -- 
  FORUM RULES (934+ members already BANNED for violation)
   
  1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
   
  2) Don't post a question in the thread of another member.
   
  3) Don't post questions regarding breaking or bypassing any security measure.
   
  4) Acknowledge the responses you receive, good or bad.
   
  5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
   
  NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
   
  
--
  To post to this group, send email to excel-macros@googlegroups.com

  -- 
  FORUM RULES (934+ members already BANNED for violation)
   
  1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
   
  2) Don't post a question in the thread of another member.
   
  3) Don't post questions regarding breaking or bypassing any security measure.
   
  4) Acknowledge the responses you receive, good or bad.
   
  5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
   
  NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
   
  
--
  To post to this group, send email to excel-macros@googlegroups.com




-- 
Sam Mathai Chacko
-- 
FORUM RULES (934+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a 

Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

[Sam Mathai Chacko]

Yes Don. I was just showing how it is done for one conditional. I had it
done for One to Five in the attachment I had posted.

Regards,

Sam

On Tue, Nov 15, 2011 at 10:07 PM, dguillett1  wrote:

>   Sam,
> > "one"
> > "two"
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>  *From:* Sam Mathai Chacko 
> *Sent:* Tuesday, November 15, 2011 10:21 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Conditional formatting of entire row
> using VBA
>
> Here's how you do it. Select your entire row. Assuming your top left cell
> in the entire selection is A1 (will be any one cell in the first column
> depending on where you started your selection)
>
> Go to conditional format, and in the options 'Use formula to determine
> which cells to format', write =$A1="One"
>
> and then apply the required format you need.
>
> Check the attachment.
>
> Regards,
>
> Sam Mathai Chacko
>
> On Tue, Nov 15, 2011 at 7:19 PM, FJO  wrote:
>
>> Hi
>>
>> Can anyone help me with the VBA code for conditional formatting in
>> Excel.
>>
>> I have af worksheet of 1000 rows with 5 colums. I have data in colum
>> B;E and would like Excel to format the rows in a colour depending of
>> what I write in colum A.
>>
>> I have 5 different text imput I can chose for each row, lets call
>> them
>> "one"
>> "two"
>> "Three"
>> "Four"
>> "Five"
>>
>> ...just to keep this example as simple as possible.
>>
>> If I write "one" in A2, I would like Excel to color Row 2 in one
>> color, if I write "Two" then another color and so on.
>>
>> I found out how to color the cell I changed, but I would like to have
>> the whole row colored.
>>
>> I hope someone can send me an example.
>>
>> --
>> FORUM RULES (934+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5)  Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE  : Don't ever post personal or confidential data in a workbook.
>> Forum owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> Sam Mathai Chacko
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Sam Mathai Chacko

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohib

Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

[dguillett1]

Sam,
> "one"
> "two"

Don Guillett
SalesAid Software
dguille...@gmail.com

From: Sam Mathai Chacko 
Sent: Tuesday, November 15, 2011 10:21 AM
To: excel-macros@googlegroups.com 
Subject: Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

Here's how you do it. Select your entire row. Assuming your top left cell in 
the entire selection is A1 (will be any one cell in the first column depending 
on where you started your selection)

Go to conditional format, and in the options 'Use formula to determine which 
cells to format', write =$A1="One"

and then apply the required format you need.

Check the attachment.

Regards,

Sam Mathai Chacko


On Tue, Nov 15, 2011 at 7:19 PM, FJO  wrote:

  Hi

  Can anyone help me with the VBA code for conditional formatting in
  Excel.

  I have af worksheet of 1000 rows with 5 colums. I have data in colum
  B;E and would like Excel to format the rows in a colour depending of
  what I write in colum A.

  I have 5 different text imput I can chose for each row, lets call
  them
  "one"
  "two"
  "Three"
  "Four"
  "Five"

  ...just to keep this example as simple as possible.

  If I write "one" in A2, I would like Excel to color Row 2 in one
  color, if I write "Two" then another color and so on.

  I found out how to color the cell I changed, but I would like to have
  the whole row colored.

  I hope someone can send me an example.

  --
  FORUM RULES (934+ members already BANNED for violation)

  1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

  2) Don't post a question in the thread of another member.

  3) Don't post questions regarding breaking or bypassing any security measure.

  4) Acknowledge the responses you receive, good or bad.

  5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited.

  NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

  
--
  To post to this group, send email to excel-macros@googlegroups.com




-- 
Sam Mathai Chacko
-- 
FORUM RULES (934+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 
--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

[Sam Mathai Chacko]

Here's how you do it. Select your entire row. Assuming your top left cell
in the entire selection is A1 (will be any one cell in the first column
depending on where you started your selection)

Go to conditional format, and in the options 'Use formula to determine
which cells to format', write =$A1="One"

and then apply the required format you need.

Check the attachment.

Regards,

Sam Mathai Chacko

On Tue, Nov 15, 2011 at 7:19 PM, FJO  wrote:

> Hi
>
> Can anyone help me with the VBA code for conditional formatting in
> Excel.
>
> I have af worksheet of 1000 rows with 5 colums. I have data in colum
> B;E and would like Excel to format the rows in a colour depending of
> what I write in colum A.
>
> I have 5 different text imput I can chose for each row, lets call
> them
> "one"
> "two"
> "Three"
> "Four"
> "Five"
>
> ...just to keep this example as simple as possible.
>
> If I write "one" in A2, I would like Excel to color Row 2 in one
> color, if I write "Two" then another color and so on.
>
> I found out how to color the cell I changed, but I would like to have
> the whole row colored.
>
> I hope someone can send me an example.
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Sam Mathai Chacko

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Conditional Format On Entire Row.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Conditional formatting of entire row using VBA

[dguillett1]

Right click sheet tab>view code>insert this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or Target.Column <> 1 Then Exit Sub
Select Case LCase(Target)
Case Is = "one": x = 1
Case Is = "one": x = 1
Case Is = "two": x = 6
Case Is = "three": x = 3
Case Is = "four": x = 4
Case Else
End Select
Rows(Target.Row).Interior.ColorIndex = x
End Sub



Don Guillett
SalesAid Software
dguille...@gmail.com
-Original Message- 
From: FJO

Sent: Tuesday, November 15, 2011 7:49 AM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Conditional formatting of entire row using VBA

Hi

Can anyone help me with the VBA code for conditional formatting in
Excel.

I have af worksheet of 1000 rows with 5 colums. I have data in colum
B;E and would like Excel to format the rows in a colour depending of
what I write in colum A.

I have 5 different text imput I can chose for each row, lets call
them
"one"
"two"
"Three"
"Four"
"Five"

...just to keep this example as simple as possible.

If I write "one" in A2, I would like Excel to color Row 2 in one
color, if I write "Two" then another color and so on.

I found out how to color the cell I changed, but I would like to have
the whole row colored.

I hope someone can send me an example.

--
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.


2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security 
measure.


4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited.


NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.


--
To post to this group, send email to excel-macros@googlegroups.com 


--
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 


NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Conditional formatting of entire row using VBA

[FJO]

Hi

Can anyone help me with the VBA code for conditional formatting in
Excel.

I have af worksheet of 1000 rows with 5 colums. I have data in colum
B;E and would like Excel to format the rows in a colour depending of
what I write in colum A.

I have 5 different text imput I can chose for each row, lets call
them
"one"
"two"
"Three"
"Four"
"Five"

...just to keep this example as simple as possible.

If I write "one" in A2, I would like Excel to color Row 2 in one
color, if I write "Two" then another color and so on.

I found out how to color the cell I changed, but I would like to have
the whole row colored.

I hope someone can send me an example.

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Help me

[Kaushal Kumar]

Hi Vijayajith,

 

Data validations can be created from one workbook to another
workbook.

 

Example:

Attached are the two workbook

1. Source_Book.xlsx

2. My_Book.xlsx

 

My list is available in Source_Book - sheet1 -
range("A1:A5")

and my validation is in workbook - My_Book.xlsx - Sheet1 -
Range("A1")

 

Procedure:

 

First create a name range in the workbook Source_Book with the
range "A1:A5" as "Month"

and now open the workbook My_Book and define a new name as My_List
as "Source_Book.xlsx!Month"

 

Now you can use this new define name "My_List" as your
validation list

 

However, it now not recommended to use a validation list from a
different workbook as the user will not be able to use the validation if the
workbook which carries the list is not open.

Best Regards,Kaushal Kumar
Date: Tue, 15 Nov 2011 19:20:58 +0530
Subject: $$Excel-Macros$$ Help me
From: vijayajith...@gmail.com
To: excel-macros@googlegroups.com

Hi Gurus,

Is this possible to do data validation from one workbook to another workbook ?
Can you give me one example?
Thanks



-- 

FORUM RULES (934+ members already BANNED for violation)

 

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

 

2) Don't post a question in the thread of another member.

 

3) Don't post questions regarding breaking or bypassing any security measure.

 

4) Acknowledge the responses you receive, good or bad.

 

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

 

--

To post to this group, send email to excel-macros@googlegroups.com
  

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


My_Book.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet


Source_Book.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Import PDF into Excel

[dguillett1]

http://tinyurl.com/7owxkgt 

Don Guillett
SalesAid Software
dguille...@gmail.com

From: Cab Boose 
Sent: Sunday, November 13, 2011 1:25 AM
To: excel-macros@googlegroups.com 
Subject: $$Excel-Macros$$ Import PDF into Excel

Hi

I would like to use the 'File-Open'  command in Excel to bring in a pdf file 
and have it finish up in .xls format.  Is there an add-in to do this 
automatically or a vba way to have it converted.  Most of the on-line require 
the conversion to excel  to be done before and creates its own workbook.  I 
will be using the same workbook each time. Deleting previous history each time.
The file is inventory listing in a stock program and only comes in PDF.

In the end result I am going to use vba to get the file off  c: drive etc, get 
into .xls, and then do some manipulating of figures etc, and produce a new 
report in .xls

My request is how to convert to .xls from within workbook.

Thanks

Charlie Harris

-- 
FORUM RULES (934+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 
--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ regarding date changing method

[NOORAIN ANSARI]

Dear Sajib,

Please try it and see attached sheet..

*Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If ActiveCell.Column = 1 And IsNumeric(ActiveCell.Offset(-1, 0).Value) =
True And Not IsEmpty(ActiveCell.Offset(-1, 0)) Then
ActiveCell.Offset(-1, 0).Value = ActiveCell.Offset(-1, 0).Value & ".11"
Exit Sub
End If
End Sub
*

Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 
On Tue, Nov 15, 2011 at 4:07 PM, Sanjib Chatterjee <
chatterjee.kolk...@gmail.com> wrote:

> Dear Mambers,
>
> any body please help.
>
> regards,
>
> Sanjib
>
>
>  On Tue, Nov 15, 2011 at 1:40 PM, Sanjib Chatterjee <
> chatterjee.kolk...@gmail.com> wrote:
>
>>
>> Dear Members,
>>
>> In excel 2003 I want to put the value in the cell (A1) as 15.11.  But
>> while changing(i.e. press enter or tab)
>>  the cell I want to  convert the figure as 15.11.2011 and so on.
>>
>> Thanking you in Advance
>>
>> Regards,
>>
>> Sanjib
>>
>>
>>
>>
>>
>> --
>> -
>>
>> --
>> FORUM RULES (934+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> -
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



--

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Sanjib.xlsm
Description: Binary data

Re: $$Excel-Macros$$ get all the datas or selected datas in different work sheets in to one sheet

[dguillett1]

Please explain the logic of your request
“I want col a & col J from which_sheets.
Please be very specific.

Don Guillett
SalesAid Software
dguille...@gmail.com

From: smitha.kumari 
Sent: Tuesday, November 15, 2011 3:20 AM
To: excel-macros@googlegroups.com 
Subject: $$Excel-Macros$$ get all the datas or selected datas in different work 
sheets in to one sheet

 

Hi Noorain,

 

Thanks for your effort. 

 

While working as per your attached file, even the headers are merged. It looks 
as if timesheets in different worksheets are pasted into  one.

 

Please the attached sheet. I need the data as in sheet 1. Only the Resource 
name and period wise hours from different worksheets is to be merged.

 

Please suggest if there is any formula too other than macros. If macros can 
only fulfill the required, then it will do.

 

 

 

Regards

Smitha/ Senior Finance Executive



World Class Solutions to Power Global Businesses

Office: 0471-2527525/2700526

smitha.kum...@speridian.com

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On 
Behalf Of NOORAIN ANSARI
Sent: Monday, November 14, 2011 9:51 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ get all the datas or selected datas in different 
work sheets in to one sheet

 

Dear Smitha,

 

try through below Code...

 

Sub Merge_Data()
Dim i, j, k As Long
k = Sheets.Count
Sheets.Add after:=Sheets(Sheets.Count)
For i = 1 To k
Sheets(i).Select
j = 2
l = Sheets(i).Range("A65536").End(xlUp).Row
Rows(j & ":" & l).Copy
Sheets(Sheets.Count).Select
Sheets(Sheets.Count).Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Next i
End Sub

On Mon, Nov 14, 2011 at 1:47 PM, smitha.kumari  
wrote:

I need help on:

 

Want to get all the datas or selected datas in different work sheets in to one 
sheet in the same work book.

Please suggest any formula.

 

Regards

Smitha 

 

-- 
FORUM RULES (934+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 
--
To post to this group, send email to excel-macros@googlegroups.com




-- 

Thanks & regards,

Noorain Ansari

http://excelmacroworld.blogspot.com/

http://noorain-ansari.blogspot.com/

 

-- 
FORUM RULES (934+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 
--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (934+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 
--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or byp

$$Excel-Macros$$ Help me

[vijayajith VA]

Hi Gurus,


Is this possible to do data validation from one workbook to another
workbook ?

Can you give me one example?

Thanks

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Convert Date to month

[Hitesh Dilipbhai Dhare]

Hi,

You may define custome date as mmm in A2 column and give reference to the date 
column that you want to have as month.
 
 
Regards,
 
Hitesh Dhare
(M) +919824004308
NWS-Gujarat
Email id: hitesh.dh...@idea.adityabirla.com
"Every Problem Is An Opportunity"

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On 
Behalf Of chhajersand...@gmail.com
Sent: Tuesday, November 15, 2011 3:20 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Convert Date to month

Dear all,

I have a date written as 01/04/2011, 03/05/2011; I want a function in next 
column to show only April and May respectively.

Thanking you in advance.

Sandeep Chhajer.
Sent on my BlackBerryŽ from Vodafone

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


ideacellular.com E-Mail Domain is now idea.adityabirla.com E-Mail Domain

An idea can change your life. 

** LEGAL DISCLAIMER 
This E-Mail may contain Confidential and/or legally privileged Information and 
is meant for the intended recipient(s) only. If you have received this e-mail 
in error and are not the intended recipient/s, kindly notify us at 
mailad...@idea.adityabirla.com and then delete this e-mail immediately from 
your system. You are also hereby notified that any use, any form of 
reproduction, dissemination, copying, disclosure, modification, distribution 
and/or publication of this e-mail, its contents or its attachment/s other than 
by its intended recipient/s is strictly prohibited and may be unlawful.

Internet Communications cannot be guaranteed to be secure or error-free as 
information could be delayed, intercepted, corrupted, lost, or contain viruses. 
IDEA Cellular Limited does not accept any liability for any errors, omissions, 
viruses or computer problems experienced by any recipient as a result of this 
e-mail.
 

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Need help on Access VBA automating outlook

[Divaker Pandey]

hi kavita,

you should add outlook reference in your excel workbook, which contains
this code

open visual basic editor (press Alt+f11)
go on tool menu
click on reference... option
Microsoft Outlook 12.0 object library

Divaker
9582060233

On Tue, Nov 15, 2011 at 11:46 AM, kavita ahuja wrote:

> Hi Group members,
>
> I have one access tool which is automating outlook. Earlier it was working
> fine on all the systems in our team
> but now our office version is migrated on office 2010.
> Now when this code is running, it is giving error on some systems in our
> team.
> I am not able to find any solution on google.
>
> Below is the code in which I am getting error.
> Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
> Dim ToContact As Outlook.Recipient
>
> Set OLF = GetObject("",
> "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
> Set olMailItem = OLF.Items.Add ' creates a new e-mail message
>
> Attached is the error message that I am getting.
>
> Please help me in this scenario
>
> Thanks & Regards,
> Kavita
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Help Required

[smitha.kumari]

Neeraj, In Excel 2007, there is only consolidate function.

 

Consolidate by position and category is not available

 

Regards

Smitha 

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Aindril De
Sent: Tuesday, November 15, 2011 4:09 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Help Required

 

Hi Neeraj,

 

You can use the Consolidate function of Excel to achieve the same. Please
follow the link below for the steps:

http://support.microsoft.com/kb/214270

 

Cheers,

Andy

On Tue, Nov 15, 2011 at 4:00 PM, Neeraj Chauhan 
wrote:

Dear Experts,

How can I make the qty against particular Indus id from the different files.

We have two sheets book 1 and 2 I want to know the sum  of quantity against
particular Indus id

Book1


 

 


Sr.No.

Period and Date

Indus ID

Site name

QTY


1

2-Oct-11

IN-158

JANDHERA

74


2

4-Oct-11

IN-158

JANDHERA

74


3

6-Oct-11

IN-158

JANDHERA

74


4

8-Oct-11

IN-158

JANDHERA

74


5

10-Oct-11

IN-158

JANDHERA

74


6

12-Oct-11

IN-158

JANDHERA

74


7

14-Oct-11

IN-158

JANDHERA

74

 


Sr.No.

Period and Date

Indus ID

Site name

QTY


1

16-Oct-11

IN-158

JANDHERA

74


2

18-Oct-11

IN-158

JANDHERA

74


3

20-Oct-11

IN-158

JANDHERA

74


4

22-Oct-11

IN-158

JANDHERA

74


5

24-Oct-11

IN-158

JANDHERA

74


6

26-Oct-11

IN-158

JANDHERA

74


7

28-Oct-11

IN-158

JANDHERA

74


8

30-Oct-11

IN-158

JANDHERA

74

 

I want to know the automatic sum of In-158

 

 

 

 

 

Thanks & Regards

Neeraj Chauhan

MIS Executive.

b4S Solutions (P) Ltd.

169/1 Mangal Pandey Nagar

Meerut UPW 251

Mob. +91-9639001150

email-neerajchauhan...@gmail.com

 

-- 
FORUM RULES (934+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com





 

-- 

 

 

-- 
FORUM RULES (934+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Help Required

[Aindril De]

Hi Neeraj,

You can use the Consolidate function of Excel to achieve the same. Please
follow the link below for the steps:
http://support.microsoft.com/kb/214270

Cheers,
Andy

On Tue, Nov 15, 2011 at 4:00 PM, Neeraj Chauhan
wrote:

>  Dear Experts,
>
> How can I make the qty against particular Indus id from the different
> files.
>
> We have two sheets book 1 and 2 I want to know the sum  of quantity
> against particular Indus id
>
> Book1
> 
>
> ** **
>
> ** **
>
> *Sr.No.*
>
> *Period and Date*
>
> *Indus ID*
>
> *Site name*
>
> *QTY*
>
> 1
>
> 2-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> 2
>
> 4-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> 3
>
> 6-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> 4
>
> 8-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> 5
>
> 10-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> 6
>
> 12-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> 7
>
> 14-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> ** **
>
> *Sr.No.*
>
> *Period and Date*
>
> *Indus ID*
>
> *Site name*
>
> *QTY*
>
> 1
>
> 16-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> 2
>
> 18-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> 3
>
> 20-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> 4
>
> 22-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> 5
>
> 24-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> 6
>
> 26-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> 7
>
> 28-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> 8
>
> 30-Oct-11
>
> IN-158
>
> JANDHERA
>
> 74
>
> ** **
>
> I want to know the automatic sum of In-158
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> *Thanks & Regards*
>
> *Neeraj Chauhan*
>
> *MIS Executive.*
>
> *b4S* *Solutions (P) Ltd.*
>
> *169/1 Mangal Pandey Nagar*
>
> *Meerut UPW 251*
>
> *Mob. +91-**9639001150*
>
> *email-neerajchauhan...@gmail.com*
>
> ** **
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



--

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ regarding date changing method

[Sanjib Chatterjee]

Dear Mambers,

any body please help.

regards,

Sanjib


On Tue, Nov 15, 2011 at 1:40 PM, Sanjib Chatterjee <
chatterjee.kolk...@gmail.com> wrote:

>
> Dear Members,
>
> In excel 2003 I want to put the value in the cell (A1) as 15.11.  But
> while changing(i.e. press enter or tab)
>  the cell I want to  convert the figure as 15.11.2011 and so on.
>
> Thanking you in Advance
>
> Regards,
>
> Sanjib
>
>
>
>
>
> --
> -
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
-

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Convert Date to month

[chhajersandeep]

Thanks for quick reply.

Sandeep Chhajer.
Sent on my BlackBerry® from Vodafone

-Original Message-
From: NOORAIN ANSARI 
Sender: excel-macros@googlegroups.com
Date: Tue, 15 Nov 2011 15:36:41 
To: 
Reply-To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Convert Date to month

Dear Chhajer,

Please try...
=Text(A1,"")

On Tue, Nov 15, 2011 at 3:19 PM,  wrote:

> Dear all,
>
> I have a date written as 01/04/2011, 03/05/2011; I want a function in next
> column to show only April and May respectively.
>
> Thanking you in advance.
>
> Sandeep Chhajer.
> Sent on my BlackBerryŽ from Vodafone
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Convert Date to month

[NOORAIN ANSARI]

Dear Chhajer,

Please try...
=Text(A1,"")

On Tue, Nov 15, 2011 at 3:19 PM,  wrote:

> Dear all,
>
> I have a date written as 01/04/2011, 03/05/2011; I want a function in next
> column to show only April and May respectively.
>
> Thanking you in advance.
>
> Sandeep Chhajer.
> Sent on my BlackBerryŽ from Vodafone
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Query on dd-mm & mm-yy format

[Suyog Kulkarni]

Hello Noorain,

Thanks for quick response but I've added two problems in same excel.
Please review & advice.

Suyog


On Tue, Nov 15, 2011 at 3:14 PM, NOORAIN ANSARI wrote:

> Dear Suyog,
>
> Please see attached sheet.
>
>
>
> --
> Thanks & regards,
> Noorain Ansari
>  *http://excelmacroworld.blogspot.com/*
> *http://noorain-ansari.blogspot.com/*
>
> On Tue, Nov 15, 2011 at 10:32 AM, Suyog Kulkarni  > wrote:
>
>> Hello Friends,
>>
>> Find attached excel file for ref.
>>
>> I'm using one software which list out required material for structure in
>> excel. Output of that excel file converts some length of "Ft-In" format in
>> "mm-dd-yy or mm-yy" format.
>> Ex: For Length 10-11, excel shows Nov-11 & For length 11-10, excel shows
>> 10/11/11.
>>
>> Somehow I need to keep all length either in text format or in Ft-inch
>> format. I'm not importing file into excel so I can't fix the "text type"
>> for particular column.
>>
>> Manual edition will be tedious as there are more than 75 pages (Each have
>> more than 1000 rows), If anybody can suggest any easy way to save time.
>>
>> Thanks & regards,
>> --
>> *Suyog Kulkarni*
>> Mailto:-suyog.kulka...@yahoo.com
>>suyog.kulka...@hotmail.com
>> Ph:-( +91 ) 9890123539
>>
>> --
>> FORUM RULES (934+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
>  --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Suyog Kulkarni
Mailto:-suyog.kulka...@yahoo.com
   suyog.kulka...@hotmail.com
Ph:-( +91 ) 9890123539

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


3042(Solved).xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Convert Date to month

[Aindril De]

Hi Sandeep,

We do not need a formula to do the same.
Simple select the column/cell that has your date and change the format to a
custom format delete everything in the "Type:" box and fill "" to
display the full month name or "mmm" to display the month in a short form.

Cheers,
Andy

On Tue, Nov 15, 2011 at 3:19 PM,  wrote:

> Dear all,
>
> I have a date written as 01/04/2011, 03/05/2011; I want a function in next
> column to show only April and May respectively.
>
> Thanking you in advance.
>
> Sandeep Chhajer.
> Sent on my BlackBerryŽ from Vodafone
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



--

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Convert Date to month

[chhajersandeep]

Dear all,

I have a date written as 01/04/2011, 03/05/2011; I want a function in next 
column to show only April and May respectively.

Thanking you in advance.

Sandeep Chhajer.
Sent on my BlackBerryŽ from Vodafone

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Query on dd-mm & mm-yy format

[NOORAIN ANSARI]

Dear Suyog,

Please see attached sheet.



-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

On Tue, Nov 15, 2011 at 10:32 AM, Suyog Kulkarni
wrote:

> Hello Friends,
>
> Find attached excel file for ref.
>
> I'm using one software which list out required material for structure in
> excel. Output of that excel file converts some length of "Ft-In" format in
> "mm-dd-yy or mm-yy" format.
> Ex: For Length 10-11, excel shows Nov-11 & For length 11-10, excel shows
> 10/11/11.
>
> Somehow I need to keep all length either in text format or in Ft-inch
> format. I'm not importing file into excel so I can't fix the "text type"
> for particular column.
>
> Manual edition will be tedious as there are more than 75 pages (Each have
> more than 1000 rows), If anybody can suggest any easy way to save time.
>
> Thanks & regards,
> --
> *Suyog Kulkarni*
> Mailto:-suyog.kulka...@yahoo.com
>suyog.kulka...@hotmail.com
> Ph:-( +91 ) 9890123539
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


3042(Solved).xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ regarding date changing method

[Sanjib Chatterjee]

Dear Ranjan Verma,

But I want I only put 15.11 in a cell, it will convert into 15.11.2011



Thanking you in advance

Sanjib


On Tue, Nov 15, 2011 at 2:12 PM, rajan verma wrote:

> HI,
> You can change Cell Format as you required like dd.mm or mm.dd
>
> On Tue, Nov 15, 2011 at 1:40 PM, Sanjib Chatterjee <
> chatterjee.kolk...@gmail.com> wrote:
>
>>
>> Dear Members,
>>
>> In excel 2003 I want to put the value in the cell (A1) as 15.11.  But
>> while changing(i.e. press enter or tab)
>>  the cell I want to  convert the figure as 15.11.2011 and so on.
>>
>> Thanking you in Advance
>>
>> Regards,
>>
>> Sanjib
>>
>>
>>
>>
>>
>> --
>> -
>>
>> --
>> FORUM RULES (934+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> Regards
> Rajan verma
> +91 9158998701
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
-

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ 10 Ways to Use Magical INDIRECT Function

[NOORAIN ANSARI]

Dear CG Kumar,

Step 1:- Retrive Camera from Excel Option-Customize-All Commands-add Camera
in Customize access toolbar then drag it on excel sheet.
Step 2:provide name range of formula * =INDIRECT("Sheet4!B"&MATCH('Indirect
Example 3'!$A$4,Sheet4!$A:$A,0))  from Formula :Define Name*
Step 3: Select Camera and put name range in Formula bar

Note : Image Should be under a Cell.

-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

On Tue, Nov 15, 2011 at 12:55 PM, C.G.Kumar wrote:

> Sir,
>
>
> Could you plz explain how did you got the pic in worksheet 3 from
> worksheet 4 as the function yields to 0 when pasted in other area.
>
> Regards,
>
>
> C.G.Kumar
>
>  On Mon, Nov 14, 2011 at 11:58 PM, NOORAIN ANSARI <
> noorain.ans...@gmail.com> wrote:
>
>> Dear Group,
>>
>> Please find attached Sheet to use 10 Simple way to use Indirect Function
>> example..
>>
>> if any one have more examples Kindly share with group.
>>
>> The Excel INDIRECT function takes a text string and converts this into a
>> cell reference.
>>
>> Excel does not understand the text string "B1" to mean a reference to the
>> cell B1. Therefore, if you extract or build up a reference to a cell or
>> range using text, you will need to use the INDIRECT function to convert
>> this into a reference that Excel can understand.
>>
>> The format of the function is :
>> INDIRECT( *Ref_text*, *A1* )
>>
>> Where the arguments are as follows :
>>   *Ref_text* - The text describing the reference *A1* - An optional
>> logical argument that defines the style of the *Ref_text* reference.
>> This can be either :
>>
>>- True - to denote that the reference is in A1 style
>>- False - to denote that the reference is in R1C1 style
>>
>> If this argument is omitted, it takes on the default value "True"
>>
>> --
>> Thanks & regards,
>> Noorain Ansari
>>  *http://excelmacroworld.blogspot.com/*
>> *http://noorain-ansari.blogspot.com/*
>>
>> --
>> FORUM RULES (934+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>   --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Indirect formula

[smitha.kumari]

Thanks  Noorain

 

Regards

Smitha 

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of NOORAIN ANSARI
Sent: Tuesday, November 15, 2011 2:49 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Indirect formula

 

Dear Smitha,

 

Please find & see attached sheet with example of below formula.


-- 

Thanks & regards,

Noorain Ansari

  http://excelmacroworld.blogspot.com/

  http://noorain-ansari.blogspot.com/

 

On Tue, Nov 15, 2011 at 12:13 PM, smitha.kumari
 wrote:

Hi,

 

Any one please explain why these quotes are used in this formula

 

=SUMIF(INDIRECT("'"&B$5&"'!A:A"),$A6,INDIRECT("'"&B$5&"'!I:I"))

 

Regards

Smitha 

 

-- 
FORUM RULES (934+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com





-- 
FORUM RULES (934+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Indirect formula

[NOORAIN ANSARI]

Dear Smitha,

Please find & see attached sheet with example of below formula.


-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

On Tue, Nov 15, 2011 at 12:13 PM, smitha.kumari  wrote:

>  Hi,
>
> ** **
>
> Any one please explain why these quotes are used in this formula
>
> ** **
>
> =SUMIF(INDIRECT("'"&B$5&"'!A:A"),$A6,INDIRECT("'"&B$5&"'!I:I"))
>
> ** **
>
> Regards
>
> Smitha 
>
> ** **
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Example.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

$$Excel-Macros$$ Need help on Access VBA automating outlook

[kavita ahuja]

Hi Group members,

I have one access tool which is automating outlook. Earlier it was working
fine on all the systems in our team
but now our office version is migrated on office 2010.
Now when this code is running, it is giving error on some systems in our
team.
I am not able to find any solution on google.

Below is the code in which I am getting error.
Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
Dim ToContact As Outlook.Recipient

Set OLF = GetObject("",
"Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
Set olMailItem = OLF.Items.Add ' creates a new e-mail message

Attached is the error message that I am getting.

Please help me in this scenario

Thanks & Regards,
Kavita

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com
<>

Re: $$Excel-Macros$$ Required Progres bar / Status bar while macro runinng....

[rajan verma]

You can use UserForm.SHow 0 ..

On Mon, Nov 14, 2011 at 6:47 PM, dguillett1  wrote:

>   Attach your file with a reply to this msg.
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>  *From:* maulik desai 
> *Sent:* Sunday, November 13, 2011 10:31 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Required Progres bar / Status bar while macro
> runinng
>
>   Hi Team,
>
> I having 1 excel sheet in that i have apply below Sumproduct macro code 72
> times to get output on my database (This code is provided by Daniel of
> this group ) my But my problem is it take about 20 to 30 mins to run the
> code total 550 row(not fixed) x 72 columns ) what I want that can i get a
> progress bar which shows me status like " 3 outof 72 columns completed" or
> 10%,15% ,30% completed like that
>
> also if can help to reduce the code size to work faster
> Please find below code provided by Mr.Daniel of this group.
>
> Thanks in advance
>
>
> Here is an example to replace sheet Batch column K with a macro:
>
> 
>
> Sub test()
>
> 'sheet Batch column K
>
> With Sheets("Batch")
>
> For Each c In .Range(.[A7], .[A65536].End(xlUp))
>
> .Cells(c.Row, 11) = Evaluate("sumproduct((DB!$A$2:$A$46803=Batch!$A" &
> c.Row & ")*(DB!$AP$2:$AP$46803=""Yes""))")
>
> Next c
>
> End With
>
> End Sub
>
> 
>
> You should run the macro each time you change, add or delete a value in
> column A; place the following macro in the sheet module :
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> If Target.Column = 1 And Target.Row > 6 Then
>
> Cells(Target.Row, 11) =
> Evaluate("sumproduct((DB!$A$2:$A$46803=Batch!$A" _
>
> & Target.Row & ")*(DB!$AP$2:$AP$46803=""Yes""))")
>
> End If
>
> End Sub
>
> 
>
> You’ll have to do the same for others formulas.
>
> Regards.
>
> Daniel
>
> 
>
> *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> *De la part de* maulik desai
> *Envoyé :* mardi 22 février 2011 18:55
> *À :* excel-macros@googlegroups.com
> *Objet :* $$Excel-Macros$$ How to use sumproduct Formula in Macro
>
> 
>
> I having Database with 4Sheets and i have used Sumproduct Formula in many
> Columns in my database. i have apply the formulas on every columns but the
> prolem is the file becomes very heavy & it take to much time to give the
> output
>
> is there any way to use the samproduct function with the help of macro ***
> *
>
> 
>
> sample file attached i am having large database 
>
> 
>
> Request you to kindly provide the solution for the same
>
> --
> Thanks & Regards,
> Maulik Desai
> 9967363926
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
>
>
> --
> Thanks & Regards,
> Maulik Desai
> 9967363926
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, se

Re: $$Excel-Macros$$ regarding date changing method

[rajan verma]

HI,
You can change Cell Format as you required like dd.mm or mm.dd

On Tue, Nov 15, 2011 at 1:40 PM, Sanjib Chatterjee <
chatterjee.kolk...@gmail.com> wrote:

>
> Dear Members,
>
> In excel 2003 I want to put the value in the cell (A1) as 15.11.  But
> while changing(i.e. press enter or tab)
>  the cell I want to  convert the figure as 15.11.2011 and so on.
>
> Thanking you in Advance
>
> Regards,
>
> Sanjib
>
>
>
>
>
> --
> -
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Regards
Rajan verma
+91 9158998701

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ regarding date changing method

[Sanjib Chatterjee]

Dear Members,

In excel 2003 I want to put the value in the cell (A1) as 15.11.  But while
changing(i.e. press enter or tab)
 the cell I want to  convert the figure as 15.11.2011 and so on.

Thanking you in Advance

Regards,

Sanjib





-- 
-

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com