VBA - Conditional Formatting 2

[MasterofNone]

The following VBA code was copied from a previous thread

http://www.tek-tips.com/viewthread.cfm?qid=223068

(this code is from acron, many thanks for getting me started):

Conditional Formatting cannot manage more that 3 conditions, so you must resort to VBA. How to approachh that best depends on how the values of the range in question are updated. Presumably it is a result of formulas, in which case trapping th esheet calculate event should assist. The following code does that :

Private Sub Worksheet_Calculate()
Dim oCell As Rang
For Each oCell In Range("A1:A20")
Select Case oCell.Value
Case Is < 1
oCell.Interior.ColorIndex = xlNone
Case Is = 1
oCell.Interior.ColorIndex = 5
Case Is = 2
oCell.Interior.ColorIndex = 3
Case Is = 3
oCell.Interior.ColorIndex = 6
Case Is = 4
oCell.Interior.ColorIndex = 4
Case Is = 5
oCell.Interior.ColorIndex = 7
Case Is = 6
oCell.Interior.ColorIndex = 15
Case Is = 7
oCell.Interior.ColorIndex = 40
Case Is > 7
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub

This nearly answers my issue but not quite. I have a list of data, eg A1:H50. The conditioning information is stored in column C. What I would like to do is format the row of information should the condition be met. i.e. if C3 =3 then it would format A3:H3. Any idea's? All help gratefully received

 

[SkipVought]

Hi,

Private Sub Worksheet_Calculate()
    Dim oCell As Range, nColorIndex
    For Each oCell In Range("C1:C50")
        With oCell
            Select Case .Value
                 Case Is < 1
                     nColorIndex = xlNone
                 Case Is = 1
                     nColorIndex = 5
                 Case Is = 2
                     nColorIndex = 3
                 Case Is = 3
                     nColorIndex = 6
                 Case Is = 4
                     nColorIndex = 4
                 Case Is = 5
                     nColorIndex = 7
                 Case Is = 6
                     nColorIndex = 15
                 Case Is = 7
                     nColorIndex = 40
                 Case Is > 7
                     nColorIndex = xlNone
             End Select
            Range(Cells(.Row, "A"), Cells(.Row, "H")).Interior.ColorIndex = nColorIndex
        End With
    Next oCell
End Sub

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Zathras]

Here is another way of saying the same thing. (Not better, just different, and a bit more compact.):

Private Sub Worksheet_Calculate()
   Dim oCell As Range, nColorIndex As Integer
   For Each oCell In Range("A1:A50")
      Select Case oCell.Offset(0, 2).Value
         Case 1: nColorIndex = 5
         Case 2: nColorIndex = 3
         Case 3: nColorIndex = 6
         Case 4: nColorIndex = 4
         Case 5: nColorIndex = 7
         Case 6: nColorIndex = 15
         Case 7: nColorIndex = 40
         Case Else: nColorIndex = xlNone
      End Select
      oCell.Resize(1, 8).Interior.ColorIndex = nColorIndex
   Next oCell
End Sub

The colon allows more than one statement on a line. Generally not a good thing to do, but I like it for Case statements.

 

[SkipVought]

Hey Z,

Slooooooo Saturday? _{I should talk!}

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}