Hi there,

I’m using Excel 2010 & looking to create a macro to save me some time implementing conditional formatting for various ranges on a spreadsheet. I’d basically work my way down the spreadsheet, the column numbers in the range of the formatting won’t change, but the row numbers will. The problem is that I’m not familiar with VBA & the macro would need some kind of input box to take the row numbers.

As you can see in the below screenshot, I have a list of countries in column B and the couriers that deliver to these destinations in column A. To the right (from column G) I am using conditional formatting to highlight the cheapest rates for each destination. To do this I use the aggregate function as explained in the youtube tutorial below the screenshot (I have slightly changed the formula so it works using rows instead of columns, but the basics are the same).

http://www.youtube.com/watch?v=xTmXOZwWYeQ

Looking to the formula that is used in the screenshot, the only values that would change are the row numbers 249 & 253 which is listed once each in the ‘Conditional Formatting Rules Manager’, & a couple of times in the ‘Edit Formatting Rule’. I have drawn arrows to highlight these numbers.

Lets pretend I want to run the macro so it will create the conditional formatting for Brunei which is shown in the screenshot..

Ideally I would run the macro, and an input box would come up asking for the first row number and the last row number. In this case it would be 249 & 253. The macro would take these values and build each formula used for the conditional formatting, then paste it in the relevant boxes, enter it & close them so it's all sorted.

I think that’s pretty much it. My boss has me working through this spreadsheet and it’s taken me hours to get down to Brunei. Since I have the rest of the alphabet to get through it would save me a considerable amount of time if I can get this macro built or automate the process somehow. I don’t know if this is asking a lot, or if there is a relatively simple solution out there, but I’d really appreciate some help! As said, I don’t know any VBA, but if it isn’t too much trouble I’d be interested to learn the process involved in building this macro, or just see how the different sections break down.

If I’ve missed anything or you have any questions, please give me a shout & I’ll do my best to answer.

Many Thanks

Peter