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

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Application.Intersect(Target, Range("b22:t22")) Is Nothing Then
      Application.EnableEvents = False
      With Target.Interior
         Select Case UCase(Target.Value)
         Case "P"
            .ColorIndex = 3
         Case "S"
            .ColorIndex = 5
         Case "HL"
            .ColorIndex = 10
         Case "ML"
            .ColorIndex = 7
         Case "FL"
            .ColorIndex = 46
         End Select
      End With
   End If
   Application.EnableEvents = True
End Sub

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

Hi there,

You may find the following code helpful:

Option Explicit


Const msRangeToMonitor  As String = "B22:T22"
Const mlMagenta         As Long = &HFF00FF
Const mlOrange          As Long = &H80FF&
Const mlGreen           As Long = &HC000&
Const mlBlue            As Long = &HFF0000
Const mlRed             As Long = &HFF&


Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lColour As Long

    If Target.Cells.Count = 1 And _
       Not Intersect(Target, Me.Range(msRangeToMonitor)) Is Nothing Then

       Select Case Target.Value
              Case Is = "P"
                   Target.Interior.Color = mlBlue
              Case Is = "S"
                   Target.Interior.Color = mlRed
              Case Is = "HL"
                   Target.Interior.Color = mlGreen
              Case Is = "ML"
                   Target.Interior.Color = mlMagenta
              Case Is = "FL"
                   Target.Interior.Color = mlOrange
              Case Else
                   Target.Interior.ColorIndex = xlColorIndexNone
       End Select

    End If

End Sub

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.