VB Code To Replace Conditional Formatting

**JimmiOO** · 03-20-2009, 08:12 AM

I have a workbook that requires a VB code to help me out with Conditional Formatting. I use Office 2003 which is restricted to 3 conditions, I know there is an add in I can use that would help me do this but other people may use this that wont have the add in. I have decided to use VB if possible to get this done.

I have a range of cells from B22 – T22 in these cells I will be putting codes, when these codes are put into the cells I would like the cells to shade a different colour depending on what code I use. Here is an example of what I mean.

P = blue

S = red

HL = green

ML = magenta

FL = orange

I may have a couple of more codes I will add at a latter time. Is it possible for VB code to do this?

**mudraker** · 03-20-2009, 08:47 AM

You may need to change ColourIndex number to suit

'These instructions pre typed & are worded to cater for the novice programmer
'To install macro to correct location

'Copy the macro
'GoTo Excel
'Select sheet this macro is to apply to
'Right Click on Sheet Name Tab > select View Code
'Paste macro into the Worksheet Module displayed

Please Login or Register  to view this content.

**Greg M** · 03-20-2009, 09:24 AM

Hi there,

You may find the following code helpful:

Please Login or Register  to view this content.

The code should be inserted into the VBA code module of the worksheet in question (not into a standard VBA code module).

All of the parameters (i.e. Range, Colours etc.) are defined as constants at the top of the module, and can easily be tweaked if necessary. The background colours are defined as COLOR values rather than COLORINDEX values (just in case you're not using the standard colour palette).

You don't mention whether or not the cells may contain values other than those you've defined as needing to be highlighted, but in any event, the "Case Else" condition caters for the situation where the value of a cell changes from (e.g.) S to a value not included in your list. In such a case, the cell background colour will change from Red to no colour (clear).

Hope this helps - please let me know how you get on.

Regards,

Greg M

**JimmiOO** · 03-20-2009, 10:04 AM

Thanks alot guys i have tried both and they both work well, just what i needed, i have went with Gregs one this time as the Case Else will be needed, all cells will contain an X before i add my code, in that case i need it to have no colour.

Thanks again.