More than 3 formatting conditions - select case?

**HelenW** · 12-11-2008, 06:38 AM

Hello again!

My next problem - I have 5 formatting conditions that I need to add in my macro and have just recently found out that I can't have more than 3 in my version of Excel - DOH.

Anyway, some research has indicated that you can use something called select case to get round this? Hopefully someone can help please!

I need to colour the range as follows:

Please Login or Register  to view this content.

When P2= 1, 2, 3 or 5 then different colours will apply (1 per number) to the above range. If P2=4 then the following range needs to be used:

Please Login or Register  to view this content.

And a different colour is used.

Is that enough info for someone to provide me with the correct code please?

Thanks!

Helen

**royUK** · 12-11-2008, 06:51 AM

Try this, change the condition ("A","B" etc) to suit

Please Login or Register  to view this content.

To add this code to your worksheet, do the following:

Copy the code that you want to use
Select the worksheet in which you the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

**royUK** · 12-11-2008, 06:57 AM

This code should work for you

Please Login or Register  to view this content.

**HelenW** · 12-11-2008, 07:16 AM

Have pasted in as suggested above but it won't run - it opens a "open macro" style box and wants me to pick one to run! So sorry to be a pain.

**royUK** · 12-11-2008, 07:25 AM

Are you sure the code is in the correct place. Attach your workbook & I will check

**royUK** · 12-11-2008, 07:28 AM

I think this code matches the ranges you are using

Please Login or Register  to view this content.

**HelenW** · 12-11-2008, 07:45 AM

Hi Roy

Have attached the workbook - hope it makes sense.

**royUK** · 12-11-2008, 07:54 AM

It looks to me that you want to check not P2 but the cell in column P of the selected cell

**HelenW** · 12-11-2008, 07:59 AM

Yes, that's right, but it worked fine when I only had 3 conditions to use, and used conditional formatting in the vb code - it kept P as absolute, then used the relevant cell next to each row to colour the range appropriately.

**royUK** · 12-11-2008, 08:03 AM

Column P values are the results of formlas and will not trigger the change event

**HelenW** · 12-11-2008, 08:07 AM

OK, so if I do paste special and just get the values, will that work?

**Shijesh Kumar** · 12-11-2008, 08:12 AM

Try the below link.. may be of help to you

http://www.excelforum.com/excel-gene...-than-3-a.html

**royUK** · 12-11-2008, 08:15 AM

If you manually enter the value in P then this code works fine

Please Login or Register  to view this content.

**HelenW** · 12-11-2008, 08:33 AM

I still can't get it to run! No idea what I'm doing wrong...

**HelenW** · 12-11-2008, 08:50 AM

Thanks Shijesh - I have tried the code below but when I run the macro (with cell on top cell of data in column P) it colors the first range of cells in the first row, but then jumps to the end of the data (in column P) and stops. Should it continue down to the next cell in column P, then do the formatting on the next range in that row?

Please Login or Register  to view this content.

**royUK** · 12-11-2008, 09:01 AM

Originally Posted by HelenW

I still can't get it to run! No idea what I'm doing wrong...

Have a look at this

**Shijesh Kumar** · 12-11-2008, 09:06 AM

Please Login or Register  to view this content.

**Shijesh Kumar** · 12-11-2008, 09:07 AM

try this

Place the cursor on the first cell containing data in column P and run macros

Please Login or Register  to view this content.

**royUK** · 12-11-2008, 09:20 AM

I think this is close to what you want

**HelenW** · 12-11-2008, 09:46 AM

Thanks - but not sure what is different! I *know* I am being stupid about this all, but I am a real beginner, and what I do is self-taught, so it's slow going! Is it the code on the data sheet? I bet you wish I hadn't asked about this now! I can't get it to do anything, anyway, so perhaps I should just give up...

**HelenW** · 12-11-2008, 09:56 AM

Shijesh - that is perfect! OK, I have 1 last thing I need to do that you might be able to help with. I need to colour ONLY cells H2, I2, L2, and M2 for my last condition - can you tell me how to add that in please?

**Shijesh Kumar** · 12-11-2008, 10:02 AM

Hi Helen,

I modified the code to color only H2 , I2 , L2 and M2... for last condition...

Please Login or Register  to view this content.

To run the code u can place the cursor anywhere in column P and run the macros...
It will format anything from that row to down (end)

**HelenW** · 12-11-2008, 10:12 AM

Brilliant - thank you so much! I tried to place a button on a different sheet to which I could assign the macro (other people will be using the sheet and I need it to be as simple as possible) but it won't work - is there a way of placing a button on a different sheet to the one in which the macro is designed to run?

Thanks again for your help.

**royUK** · 12-11-2008, 10:12 AM

Originally Posted by HelenW

Thanks - but not sure what is different! I *know* I am being stupid about this all, but I am a real beginner, and what I do is self-taught, so it's slow going! Is it the code on the data sheet? I bet you wish I hadn't asked about this now! I can't get it to do anything, anyway, so perhaps I should just give up...

If this refers to my last example, then that version updates the coloured cells each time the sheet calculates, avoiding the need for running the macro. You could also make it update on opening the workbook.

**Shijesh Kumar** · 12-11-2008, 10:28 AM

Originally Posted by HelenW

I tried to place a button on a different sheet to which I could assign the macro (other people will be using the sheet and I need it to be as simple as possible) but it won't work - is there a way of placing a button on a different sheet to the one in which the macro is designed to run?

For that u need to add a small code... and assing it to macros

Please Login or Register  to view this content.

Further , it would be much better to do the way.. Roy told.. it add this code..

Please Login or Register  to view this content.

**HelenW** · 12-11-2008, 10:51 AM

Thank you for the help Roy but I am really struggling to understand how your example works. I think my main trouble, aside from not know a huge amount of vb code, is *where* to put the code within each workbook to make it do what I want! I didn't see that you'd attached another file, so will go and have a look at that now.

**royUK** · 12-11-2008, 10:53 AM

Originally Posted by HelenW

Thank you for the help Roy but I am really struggling to understand how your example works. I think my main trouble, aside from not know a huge amount of vb code, is *where* to put the code within each workbook to make it do what I want! I didn't see that you'd attached another file, so will go and have a look at that now.

The last example that I posted has all the code in place and is triggered by the calculate event of the sheet - see in the VB Editor WorkSheet_Calculate.

Every time the workbook calculates the colour coding will update.

**HelenW** · 12-11-2008, 11:01 AM

Thank you - I think I am beginning to understand. In this instance, we will always be pulling in data from access to excel, running the macro to do various formatting and date calculations, then I wanted to add the colour formatting on afterwards - will your addition still work? I decided the spreadsheet was far too messy with too many test macros recorded, so I have changed to another one and will add your code in where you did on the last example - I did get Shijesh's code to work perfectly for the colour coding I want, but I have to run a separate macro for that, when the cursor is in the right place!

**Shijesh Kumar** · 12-11-2008, 11:04 AM

Hi Helen,

I have create a new module called module 8 ...
See the attachement

There is also one more code in sheet1(Data) double click on tht to check the code
this code is needed if you dont want to use button and automate it....

**royUK** · 12-11-2008, 11:10 AM

You can call the addFormat macro from a button. Just add a button from the Forms ToolBar and assign the macro when prompted

**HelenW** · 12-11-2008, 11:30 AM

I think I'm there - thank you to both of you SO MUCH for all your help. I ended up using Shijesh's code, but only as I could understand how to make it work straightaway - so we will have to put up with a couple of extra button clicks until I can understand more what Roy was telling me to do!

Think I will add this thread to my favourites so I can study the examples in more detail later!

Thank you again - gratefully appreciated.

**royUK** · 12-11-2008, 11:40 AM

I've added a button & some notes to the code

**HelenW** · 01-07-2009, 12:23 PM

Hello all

I'm working with my code again, and I have got the below in place:

Please Login or Register  to view this content.

This adds a row every time there is a change in name in column F. For some reason though, it doesn't recognise the difference between "Southern" and "Strategic and Corporate" - can any one advise why?

Also, once I can get that bit sorted, I would like to apply specific formatting to the blank rows that are added (each one has a different colour that spans across the data) and the name from the Area column that corresponds to that section needs to be added into the coloured row (also with some formatting e.g. bold). Finally, I would want to delete the Area column once everything is done.

I've attached the spreadsheet I am working with, with some data that I've been using, to show what the macro does up until the point when the blank rows are entered.

I've also attached a spreadsheet that shows what the data needs to look like once everything is done!

**shg** · 01-07-2009, 01:44 PM

For some reason though, it doesn't recognise the difference between "Southern" and "Strategic and Corporate"

Your workbook contains no such examples.

**HelenW** · 01-08-2009, 04:58 AM

Oh, I attached 2 spreadsheets - the 2nd one hasn't attached. I know it doesn't say Strategic and Corporate in the above attached spreadsheet but it's the latest data that I have to work with and it doesn't include any incidences of S&C! The words are in the Area column and it definitely doesn't recognise the difference.

Not sure if my "finished" formatted spreadsheet has attached this time.