Spy Journal 3.0 Filtering the web for you

Sort of like liquorice all-sorts – only not.

NowPublic can scan keywords, heres the Drupal scan page. This article should get linked on there.

I got nominated as a Web 2.0 and Enterprise 2.0 expert. This might prompt me to write some articles here in the future about the difficulties in establishing Enterprise 2.0 ideas in small and large business. Watch this space! Thanks John for that!

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.