Macro for formatting fonts and cell colours - not Conditional Formatting

Hi all.

I've got a spreadsheet that I need to format according to different cell values.

I have a list in column K where the user can select the probability percentage of winning a project. These values are 25, 50, 75 or 100. I want the text in rows from columns A to L to change to a specific colour based on which value is selected (Red for 25, Orange for 50, Blue for 75 and Green for 100).

I also have a list in column L where the user can select the current status of the project. These values are "Concept", "Design", "Tender - Main", "Tender - Sub", "Applicator Appointed" and "Awarded". I want the background colour of the row from columns A to L to change to a specific colour based on which value is selected (RGB 255, 229, 238 for Concept; RGB 217, 240, 255 for Design; RGB 229, 255, 232 for Tender - Main; RGB 252, 255, 222 for Tender - Sub; RGB 255, 239, 224 for Applicator Appointed; RGB 244, 235, 255 for Awarded), whilst maintaining the text colour that was applied according to column K.

I can do either of these via conditional formatting, but not both.

I've searched and searched but due to my extremely limited VBA experience, I just can't seem to find anything that I can adapt for this purpose.

Does anyone have a macro formula that will help me achieve this?

Many thanks, Kay.

Can you please attach the spreadsheet? It will be easier to test the formats.

Do you have Excel 2003 or later? If Excel 2003 you only have 3 conditions (4 if you count the default), which means you are stuffed, as you would need 16. If you have 2007 or 2010 use formula rules in the setup all of the conditions and make sure the Stop if True checkbox is not checked.

Thanks heaps for the replies.

I've attached a sample of the spreadsheet. I'm using Excel Mac:2011.

snb: I tried your code but got a runtime error "13", type mismatch.

Cheers.

Kayaness,

You can use a Select Case Statement in the Last Column or wherever you want the FillCOlour to be changed based on the DATA..

Please Login or Register  to view this content.

Regards
e4excel