Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Cond. Formatting

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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....
    Kind Regards,
    Ivan F Moala From the City of Sails

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Brampton
    Posts
    328
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •