VBA Help #6

I know this is simple so I apologize for posting it.  I'm looking for
the VBA code to take a sheet and sort the rows (below row 2) by a
certain column, descending.  After sorting, I want Excel to provide
shading in alternating rows.  For example:

Row 2 - no shading
Row 3 - Shading
Row 4 - no shading
Row 5 - shading.

Thanks in advance for the help.
0
1/25/2011 3:47:20 PM
excel 39879 articles. 2 followers. Follow

2 Replies
545 Views

Similar Articles

[PageSpeed] 0

On Jan 25, 10:47=A0am, SHalper <scott.m.hal...@gmail.com> wrote:
> I know this is simple so I apologize for posting it. =A0I'm looking for
> the VBA code to take a sheet and sort the rows (below row 2) by a
> certain column, descending. =A0After sorting, I want Excel to provide
> shading in alternating rows. =A0For example:
>
> Row 2 - no shading
> Row 3 - Shading
> Row 4 - no shading
> Row 5 - shading.
>
> Thanks in advance for the help.

hi
you are a tad short on info concerning the sort and shading but....
try this and see if it does what you want.
Sub sortncolorit()
Dim r As Range
Dim lr As Long
Dim rr As Range
Set r =3D Range(Range("A2"), Range("A2").End(xlDown).End(xlToRight))
'the above line assumes a solid block of data.
Set rr =3D Range("A3")

r.Sort Key1:=3DRange("A2"), Order1:=3DxlDescending, Header:=3DxlGuess, _
    OrderCustom:=3D1, MatchCase:=3DFalse, Orientation:=3DxlTopToBottom, _
    DataOption1:=3DxlSortNormal

lr =3D Cells(Rows.Count, "A").End(xlUp).Row
Set rr =3D Range(rr, "A" & lr)

For Each n In rr
    If n.Row Mod 2 =3D 1 Then
         n.EntireRow.Interior.ColorIndex =3D 15  'gray
    End If
Next n
End Sub

you may need to change the sort key. i used column A.
also for the shading, l chose gray. see this site for other excel
colors index numbers.....
http://www.mvps.org/dmcritchie/excel/colors.htm

if this don't do what you want, post back with more info.
regards
FSt1
0
fst1one (4)
1/28/2011 6:22:25 AM
In article <9ebbb645-31b3-468a-8471-1d62865d67a9@z31g2000vbs.googlegroups.com>, 
FSt1 at fst1one@yahoo.com says...
> 
> On Jan 25, 10:47�am, SHalper <scott.m.hal...@gmail.com> wrote:
> > I know this is simple so I apologize for posting it. �I'm looking for
> > the VBA code to take a sheet and sort the rows (below row 2) by a
> > certain column, descending. �After sorting, I want Excel to provide
> > shading in alternating rows. �For example:
> >
> > Row 2 - no shading
> > Row 3 - Shading
> > Row 4 - no shading
> > Row 5 - shading.
> >
> > Thanks in advance for the help.
> 
Why not use conditional format based on row number ?


0
NOSPAM6499 (14)
1/28/2011 6:26:41 PM
Reply:

Similar Artilces:

HELP #2
I recently cleaned my hard drive to the bone and reinstalled everything on my Windows 98SE computer. Now when I run Publisher 2000, after about 15 seconds up pops a window that says, "There was a problem starting the Office Assistant. Would you like to try reinstalling it?" I have the stand alone version of Publisher 2000. I've tried the "Repair" option without success. Isn't the Ofc Asst the paper clip in Word? I have Word 97. Anyone know the fix? -- Don -------- Vancouver, USA - One of the great cities in one of the 45+ countries in America! OFF2000: &q...

Excel exact copy workbook to workbook HELP NEEDED !!
I have looked everywhere for this answer and I turn to the news groups to find the answer. I wish to copy a group of formulas from one excel workbook to another excel workbook. I could drag and drop but all the fomulas now reference the original workbook in their formulas. This is not what I wish to see. I need it to be an exact copy. I am dealing with way too much data to retype it all. Basically I want page six of the second workbook to look exactly like page six from the original workbook. John, Try this in Windows Explorer. Select the file. Menu pick edit/copy, then edit/ past...

Please help!!! Manually removal of Exchange 5.5!
I need to manually remove my old exchange 5.5 from the AD and my e2k, but I am not sure how much to delete! Is it just the server or is it anything else? I have tried every thing I could fined on the Microsoft web! Well, it's a big task. I guess you have the Exchange 2003 server in place in your org. You need to remove the registry keys as well as edit the ADSI to remove the Exchange 2000. You better read the docs. Search the MS site for Manually removing Exchange articles. Cheers ! Ruwan Dissanayake >-----Original Message----- >I need to manually remove my old exchang...

Time Sync help
Does anyone have an example or article handy of a way to sync workstations time w/ a server? Are you talking about a Server and WorkStation that you have created. If so I have done the exact code in the past, however, not sure if I can help. It all depends on how your server and work station communicate. This can be further complicated if you have a server running along with several work stations.. across time lines. "Kelly Brimstone" <kellybrimstone@yahoo.com> wrote in message news:7d3b4b05.0309021126.49bb14d7@posting.google.com... > Does anyone have an example or articl...

Send As help #2
Can you guide me to information about how to properly set up a "send as" account? The user uses the "send from" field with 3 other email addresses and is getting rejections intermittently. Also is getting an Lsasrv 40961 error intermittently in his event log. In news:E4A65D7E-39A9-425D-9F04-5FF435482FF9@microsoft.com, Tony K <TonyK@discussions.microsoft.com> typed: > Can you guide me to information about how to properly set up a "send > as" account? > The user uses the "send from" field with 3 other email addresses and > is gettin...

Help with adding x-axis major gridlines
I'm trying to make a chart that will have small tick-marks along the x-axis for a fiscal week, and a long tick-mark separating the fiscal month. I think I'm not formatting the data properly in the worksheet. Any ideas? Use a Line chart with dates as the X values. Double click the X axis. On the Scale tab, set the X axis minimum to occur on the 1st of a month. Set the Base Unit to Day(s), the Major Unit to 1 Month(s), the Minor Unit to 7 Day(s). On the Patterns tab, choose whatever for Major Ticks and something besides None for Minor Ticks. Click OK. On the Chart menu, choose C...

Formulas in a paragraph???HELP!!!
How would the formulas go in the below paragraph? This letter will confirm that Vision Financial Corp will accept =c9 as full payment of debt; you may take advantage of this special offer immediately by contacting us at . Once credit or check payment by phone is taken, processed and applied to the account, the account will be considered settled in full. =A9 will be released of any further financial liability converning repayment of this account. How many times are you going to ask this question? What is wrong with the responses you've already received? You have many of ...

Help! Money hangs on startup
Hi all, I had to hard reset my laptop, and after this Money hangs at startup. It plays the startup sound and hangs. What could be the problem here? Thanks in advance. -- Johan Johan Parin wrote: > Hi all, > > I had to hard reset my laptop, and after this Money hangs at startup. It > plays the startup sound and hangs. What could be the problem here? > The problem was solved by removing the following files: My Money.M14 My Money.lrd My Money Backub.mnf -- Johan ..M14 is a backup Money 2005 file from a Money 2006 upgrade. Removing it eliminates your roll-back ability but...

Need Help with Creating a Multi-part Rule for Outlook 2003
Hi all -- Has anyone else come across the need to do the following: * Have a rule that copies and then forwards all non-domain generated e-mail to another e-mail address. For example: - Have all e-mail sent by anyone within the xyz.com domain to remain in the Inbox, but anything not sent by someone within xyz.com will be copied and forwarded to another recipient. If any one knows of a way to accomplish the above or if it is not possible, please reply here. Sincerely, Ralph Greenberg, MCSA Set a rule to copy/forward all mail items (no defining crit...

Error accessing Help files
I am unable to use any of the hyperlinks in the Help topics. I can select a topic, which opens right up, but then if I click on Related Topics or any of the other hyperlinked topics, I get a script error. I am running Windows 2000, Version 5.0. My Publisher is version 2000 SR-1. Suggestions? After managing to set up OE-QuoteFix on his new PC, Ed reads a message from TJF <tomme_fent@iand.uscourts.gov>... > I am unable to use any of the hyperlinks in the Help > topics. I can select a topic, which opens right up, but > then if I click on Related Topics or any of the other...

Rookie-building DB-want to get right the first time! Help?
Rookie user: Access 2007, Using “Picture Yourself Learning: Microsoft Access 2007” as reference guide. Need suggestions for how to structure what seems to me to be a very complicated DB. I want to build it the best way the first time, so I don’t do a lot of work and not have it do what I need. Here’s what I need to do: Track GIS datasets for about 50 natural and environmental hazards. The data itself does not need to be tracked. I do need to track its source (National, State, County or City data) and know its date of creation, and frequency of updates for example. I don’t k...

How to start the default mail program using Excel VBA? #2
From Excel (using VBA) I want to click a button and start the default mail program installed on the computer. After this I want to prepopulate the TO line in Outlook Express or Outlook (depending on what's installed on the machine) with a few e-mail addresses (stored in my spreadsheet). Any sample code for this please? Thanks in advance Michalakis Michael michalakis_michael@hotmail.com ...

Microsoft Outlook #6
How do you forward all office email from your office to your home computer using Microsoft Outlook XP? ...

Please Help
i put vba textbox in word 2002, i exit design mode and saved BUT.. every time i open it up it opens up in design mode!!!!! what can i do to make the file open not in design mode??? i thought off writing in document_open : close the design mode, but i dont know the code line. all answers will do.. thenxs I bet you'd get better answers in an MSWord newsgroup (as opposed to an Excel newsgroup). DirectD@gmail.com wrote: > > i put vba textbox in word 2002, i exit design mode and saved BUT.. > every time i open it up it opens up in design mode!!!!! > > what can i do to mak...

Outlook 2002 Notes Connector help
I use Outlook 2002 (Offix XP SP2) with the Outlook Notes connector and access a Notes 5.x database running under W2K Server SP3. For some reason, this seems to cause the Notes service on the server to hang when accessing mail. It hangs even faster if I try to copy email from Notes to a PST. Has anyone seen this problem? Is there a fix? Thanks! ...

Excel Help Please quick question
i want to have a cell automatically fill its self in when another cell says a certain thing. eg - if i type "blue" into D5 i want D6 to automatically change to "yes" Is this possible Thanks In Cell D6 put this formula =3DIF(D6=3D"blue","yes","") On Nov 15, 7:13=A0pm, Tom Jacques <Tom Jacq...@discussions.microsoft.com> wrote: > i want to have a cell automatically fill its self in when another cell sa= ys a > certain thing. > > eg - if i type "blue" into D5 i want D6 to automatically change t...

Exchange 5.5 on NT Recovery help!
Hello, I'm looking for some general direction on recovering an Exchange 5.5 server on NT... We need to recover a NT 4.0 Exchange 5.5 server from 2003.. The server was backed up directly to tape using NT Backup. We have two options... 1. If we have the original server, I should be able to load NT, SP6a, then perform the restore from tape... Should work.. 2. If we don't have the original server, I'm hoping to simply restore the Exchange databases onto another NT/Exchange test server we have. Mount the store (somehow), and export the mailbox we need.. The bottom line i...

Need help printing multiple pages in VB.Net 2008
I am having a problem printing multiple pages with a common header/footer etc in VB 2008. I have the following code which will print a single page exactly as I need it. '***************** Print button click event ************ Private Sub cmdPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrint.Click pdPrintCal.PrinterSettings.PrinterName = lblCurPrt.Text pdPrintCal.DefaultPageSettings.Landscape = True If chkPrtPView.Checked Then pvPrintCal.Document = pdPrintCal pvPrintCal.Icon = Me....

Outlook Tasks #6
I'm using Outlook 2003 on an Exchange Server. I'm a manager and I assign a task to someone and they accept it. They leave the company without the task being marked completed. I wish to "recall" the task (similar to recalling a message) and assign it to someone else. ...

Anyone intrested in helping a pathetic charity case?
Yes, the time has come for me to admit my failure and beg for someone to assist is setting up my server to use Exchange 2003 in place of the built-in SMTP/POP3 in windows server 2003. I can'r figure it out and Microsoft is absolutely no help at all. They can't even get a server product to work properly out of the box, why would they care to help fix their mess. if any system admins would like to help send me an e-mail to gmaier@tampabay.rr.com or MSN me Thanks desperate. Can you post details of the issue here? -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/b...

Need help converting "InstalledOn" date for all Win32_QuickFixEngineering entries
Hello, all. I'm new to Powershell scripting, but have used VBScript for years. I've found that there is no way in VBScript to do the following and am hoping someone can help with a PowerShell script for the following: I need to be able to document all of the HotFixIDs, their description, and the date that the HotFixes were installed. I want this list to be a CSV list available in the root directory of the computers that need this information. All of the computers that I'm going to use this on are 64-bit computers and are running Windows Server 2008 and Vista. I found ...

Outlook 2007 doesn't show entire message!! HELP please!
I have been searching for a probmlem i have with Outlook 2007. I am recieving emails fine, but what i noticed the other day, is that when i open the message with my PC, only part of the mesage is shown, the part that is from the sender, but when it includes a forwarded text, it will not show!! this is a huge problem.. once i missed an important notice because of this. But this problem does not occur when i open the mail with Mac. Do you have a virus scanner installed which integrates itself with Outlook? Uninstall this integration part of your virus scanner and try again; you'...

VBA Error
I have a Excel Spreadsheet which runs without problems on both Windows 2000, and Windows XP, but when I run the spreadsheet on my laptop with Windows 2000 I get the following error code. Microsoft Visual Basic Run-time error '9' Subscript out of range code stops hear: With Workbooks("Time_Sheet").Worksheets("Data_Entry").Range("A1").SpecialCells(xlCellTypeLastCell) End With With Range("A1").SpecialCells(xlCellTypeLastCell) Row = .Row Workbooks("Time_Sheet").Worksheets("Data_Entry").Names.Add Name:="Database", ...

ldifde HELP
I am try to generate a list of all users that forward mail to an alternate mailbox recepient.. I found the command in this newsgroup but it does seems to work. Can someone take a look at let me know what I am missing ???. I am running Win2003 sp1 and exchange 2003 sp2 ldifde -f output.ldf -r "(objectClass=user)" -l displayname,altrecipient Thanks "Darren@community.nospam" <GQ@community.nospam> wrote: >I am try to generate a list of all users that forward mail to an alternate >mailbox recepient.. I found the command in this newsgroup but it does seems ...

Encrypt a Word document in VBA
I want to create a macro that in certain situations will encrypt the current document such that when the document is saved and opened again, Word will ask for the password (similarly to the user selecting the Office Button -> Prepare -> Encrypt Document). Would someone tell me how this can be done? Thanks for any help. To set a password ... ActiveDocument.Password = "whatever" To save with a password ... ActiveDocument.SaveAs name_etc., Password:="whatever" -- Enjoy, Tony www.WordArticles.com "JeffG" <JeffG@dis...