Cond. Formatting

[RoyinUK2000]

I would like to colour rows depending on six different values. Can someone please tell me how to apply conditional formatting for say six values in vb.

Thanks
Roy

 

[Ivan F Moala]

On 2002-04-29 03:31, RoyinUK2000 wrote:
I would like to colour rows depending on six different values. Can someone please tell me how to apply conditional formatting for say six values in vb.

Thanks
Roy

You will need to use an event for this.
What is the criteria, colour & range to monitor....

 

[RoyinUK2000]

The range of cells is A to H and is growing downwards. The B column will contain a value of either ACID ALKALINE SULPHONE NON-HAZ REBLEND. I want the rows to be different colours dependant on what is in column B. I can do this for 3 values in excel but want to know how to do this for more than 3 values by using code.

Thx
Roy

 

[Corni]

Based on the eight column table (A:H) and with five conditions for the product type (in column B), this code will colour format the records, driven by the Worksheet_Change event. Because of the Select Case statement, you can have actually as many conditions as you wish:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim prodType As String
Application.EnableEvents = False
If Target.Column = 8 Then
ActiveCell.Offset(0, -7).Select
prodType = ActiveCell
Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, 6)).Select

Select Case prodType
Case "ACID"
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Case "ALKALINE"
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Case "SULPHONE"
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Case "NON-HAZ"
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Case "REBLEND"
With Selection.Interior
.ColorIndex = 12
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Select
ActiveCell.Offset(1, 0).Select
End If
Application.EnableEvents = True
End Sub