Spy Journal 3.0 Filtering the web for you

I had an interesting question about conditional formatting posed in the comments by Stephen.

In a new sheet, I am trying to make a whole row turn red, green or amber depending on the value of one cell in that row, so I can easily see which jobs we have won, lost or are pending. Any 'IF' conditional formula I write gets thrown out by Excel. What am I doing wrong?

I promised him an answer so here it is.

For this exercise I am making some assumptions.

• You are using Excel 2007 format Excel spreadsheet (.xlsx or .xlsm). These instructions will not work in detail for Excel 2003, though the concept is similar.
• That there are 3 conditions we  are looking for. Of course Excel 2007 allows more than 3 conditions so you can add more if you need. (One of the improvements on Excel 2003 that only allowed 3 rules)
• That the entire row is needed to be coloured. If you need a smaller section than change the formulas accordingly.
• That the entire worksheet needs this formatting. If you need a smaller section than change the formulas accordingly.
• That the conditional formats are going to be based on a cell that returns a specific result based on some other rule.

I promised to start writing a series of VBA primers. This is the first one.

For many using Excel is a daily thing. From office secretaries who manage the petty cash through to financial whizzkids who monitor stocks and bonds, Excel is a very versatile tool. It also has a lot of potential for automating repetitive tasks easily by being able to create macros.

A macro is a simply a “recorded set of instructions”. These can be as simple as saving two different sets of print options so you don’t have to continually change them. Basically the concept of a Macro is that you can get the computer to record the key and mouse strokes you use and save them so they can be repeated. While the reality is that the macro that is recorded is written in a language called VBA (visual Basic for Applications) this is irrelevant to people who first start using it.

I have been requested numerous times recently by readers in comments and emails to write some how to articles for VBA. It is my intention to start to do this.

My basic plan will be to take situations where new VBA users record some code with the VBA recorder, and then want to try and make it more robust.

An article by Nick Hodge (the Excel Nick Hodge) on Dicks Daily Dose of Excel website illustrates my point.

I probably will not write articles as long or as in depth as he has done. Instead I will focus on small parts of this code such as range selection, switching workbooks, defining variables and constants and using them etc. Specifically each article I write will have real practical application. E.g how to copy data from one file to another, how to save a spreadsheet as values for emailing etc.

In the meantime enjoy Nicks article on his coding technique particularly the latter stages where he talks about the progression in code development in VBA.

I have something like 20 or more websites open in browser that I want to be able to review or recommend. I haven’t got time so I will link them here, tag on delicious and clip into socialmedian as appropriate.

Here goes.

Microsoft

• Windows Live One Care for Server. Free trial. Works on Server 2008 Standard 64 bit, SBS 2008 Standard and Premium.
• File Dialogs – adding My Places in the File Open and File Save As dialog boxes. Quickly find frequently used locations on your PC or network.
• Adding signatures to email in Windows Live Hotmail
• Plugins – Flickr for Live Writer, Flickr for Outlook, Flickr for Word. Zemanta for Windows Live Writer. Hat tip to Sarah Perez from Channel 10 for this. (see our Geek Girl profile on Sarah)
• Another from Sarah is the Circle Dock Application launcher. Go straight to Circle Dock.
• How to share large files without attaching them to email
• Bulk convert old documents to new Office 2007 format from Digital Inspiration. I will be trialing this with a set of copied documents to test the results.