locked
programming conditional formatting rules - excel vba RRS feed

  • Question

  • I need to make a macro that apply some conditional formatting rules (like if a cell value is greater than 0 then color must be green etc, if it is equal with 0 then it should be black, <0 then green...) on selected cells (selection cells are from different columns or rows).

     Please help me asap!

    Thank you

    Monday, October 4, 2010 9:26 AM

Answers

  • The macro recorder is a wonderful tool for creating macros like what you describe.  You will get code like

     

        Range("C4:F11").Select
        Selection.FormatConditions.Delete
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="0"
        Selection.FormatConditions(1).Interior.ColorIndex = 6

    Just remove any lines like the first: Range("C4:F11").Select   and your macro will work on the current selection.

    If you have problems generalizing your code, post back with what you record, and we will help you change it to fit.

    Bernie

    • Marked as answer by ucsel Monday, October 4, 2010 5:00 PM
    Monday, October 4, 2010 3:42 PM
  • Thanks!

    This is my code:

    Sub Buton95_Clic()

    Selection.FormatConditions.Delete
    With Selection.FormatConditions _
            .Add(xlCellValue, xlEqual, "=0")
            .NumberFormat = "#,## 0.00"
        With .Font
            .ColorIndex = 1
        End With
    End With
    With Selection.FormatConditions _
            .Add(xlCellValue, xlGreater, "=0")
                  
        With .Font
            .ColorIndex = 10
        End With
        .NumberFormat = ChrW(9650) & " #,## 0.00"
    End With
    With Selection.FormatConditions _
            .Add(xlCellValue, xlLess, "=0")
                  
        With .Font
            .ColorIndex = 3
        End With
        .NumberFormat = "#,## 0.00;" & ChrW(9660) & "#,## 0.00"
    End With
    With Selection.FormatConditions _
            .Add(xlCellValue, xlEqual, "=")
                   .NumberFormat = "#,##0.00"
        With .Font
            .ColorIndex = 2
        End With
    End With
    End Sub

    How can I pass the Selection as a parameter to a function call?


    adrian socolescu
    • Marked as answer by ucsel Monday, October 4, 2010 5:00 PM
    Monday, October 4, 2010 3:49 PM
  • Functions and subs accept objects as parameters. Simply replace all the Selection object references with an identifier like TargetRange, and add that parameter to the block header. E.g.:

     

    Sub ApplyFormatting(TargetRange as Range)

    [your code, with TargetRange replacing Selection]

    End Sub

     

    When you call the function, just write:

    ApplyFormatting Selection

    • Marked as answer by ucsel Monday, October 4, 2010 5:00 PM
    Monday, October 4, 2010 4:14 PM
  • Okay, it is like that:

    Sub Change_Condition(target As Range)
    target.FormatConditions.Delete
    With Selection.FormatConditions _
            .Add(xlCellValue, xlEqual, "=0")
            .NumberFormat = "#,## 0.00"
        With .Font
            .ColorIndex = 1
        End With
    End With
    With target.FormatConditions _
            .Add(xlCellValue, xlGreater, "=0")
                  
        With .Font
            .ColorIndex = 10
        End With
        .NumberFormat = ChrW(9650) & " #,## 0.00"
    End With
    With target.FormatConditions _
            .Add(xlCellValue, xlLess, "=0")
                  
        With .Font
            .ColorIndex = 3
        End With
        .NumberFormat = "#,## 0.00;" & ChrW(9660) & "#,## 0.00"
    End With
    With target.FormatConditions _
            .Add(xlCellValue, xlEqual, "=")
                   .NumberFormat = "#,##0.00"
        With .Font
            .ColorIndex = 2
        End With
    End With
    End Sub

     

    but how can I pass the Selection to this function?

    Change_Condition (Selection)

    That gave me an error: '438' Object doesn't support this property or method

     

    Thank you


    adrian socolescu
    • Marked as answer by ucsel Tuesday, October 5, 2010 3:37 PM
    Tuesday, October 5, 2010 6:40 AM
  • 'but how can I pass the Selection to this function?

    Lose the parens:

    Sub test()
    Change_Condition Selection

    End Sub

    And you had one selection still in the macro:

    Sub Change_Condition(target As Range)
    target.FormatConditions.Delete
    With target.FormatConditions.Add(xlCellValue, xlEqual, "=0")
        .NumberFormat = "#,## 0.00"
        .Font.ColorIndex = 1
    End With
    With target.FormatConditions.Add(xlCellValue, xlGreater, "=0")
        .Font.ColorIndex = 10
        .NumberFormat = ChrW(9650) & " #,## 0.00"
    End With
    With target.FormatConditions.Add(xlCellValue, xlLess, "=0")
         .Font.ColorIndex = 3
        .NumberFormat = "#,## 0.00;" & ChrW(9660) & "#,## 0.00"
    End With
    With target.FormatConditions.Add(xlCellValue, xlEqual, "=")
        .NumberFormat = "#,##0.00"
        .Font.ColorIndex = 2
    End With
    End Sub

    • Marked as answer by ucsel Tuesday, October 5, 2010 3:37 PM
    Tuesday, October 5, 2010 3:03 PM

All replies

  • The macro recorder is a wonderful tool for creating macros like what you describe.  You will get code like

     

        Range("C4:F11").Select
        Selection.FormatConditions.Delete
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="0"
        Selection.FormatConditions(1).Interior.ColorIndex = 6

    Just remove any lines like the first: Range("C4:F11").Select   and your macro will work on the current selection.

    If you have problems generalizing your code, post back with what you record, and we will help you change it to fit.

    Bernie

    • Marked as answer by ucsel Monday, October 4, 2010 5:00 PM
    Monday, October 4, 2010 3:42 PM
  • Thanks!

    This is my code:

    Sub Buton95_Clic()

    Selection.FormatConditions.Delete
    With Selection.FormatConditions _
            .Add(xlCellValue, xlEqual, "=0")
            .NumberFormat = "#,## 0.00"
        With .Font
            .ColorIndex = 1
        End With
    End With
    With Selection.FormatConditions _
            .Add(xlCellValue, xlGreater, "=0")
                  
        With .Font
            .ColorIndex = 10
        End With
        .NumberFormat = ChrW(9650) & " #,## 0.00"
    End With
    With Selection.FormatConditions _
            .Add(xlCellValue, xlLess, "=0")
                  
        With .Font
            .ColorIndex = 3
        End With
        .NumberFormat = "#,## 0.00;" & ChrW(9660) & "#,## 0.00"
    End With
    With Selection.FormatConditions _
            .Add(xlCellValue, xlEqual, "=")
                   .NumberFormat = "#,##0.00"
        With .Font
            .ColorIndex = 2
        End With
    End With
    End Sub

    How can I pass the Selection as a parameter to a function call?


    adrian socolescu
    • Marked as answer by ucsel Monday, October 4, 2010 5:00 PM
    Monday, October 4, 2010 3:49 PM
  • Functions and subs accept objects as parameters. Simply replace all the Selection object references with an identifier like TargetRange, and add that parameter to the block header. E.g.:

     

    Sub ApplyFormatting(TargetRange as Range)

    [your code, with TargetRange replacing Selection]

    End Sub

     

    When you call the function, just write:

    ApplyFormatting Selection

    • Marked as answer by ucsel Monday, October 4, 2010 5:00 PM
    Monday, October 4, 2010 4:14 PM
  • Okay, it is like that:

    Sub Change_Condition(target As Range)
    target.FormatConditions.Delete
    With Selection.FormatConditions _
            .Add(xlCellValue, xlEqual, "=0")
            .NumberFormat = "#,## 0.00"
        With .Font
            .ColorIndex = 1
        End With
    End With
    With target.FormatConditions _
            .Add(xlCellValue, xlGreater, "=0")
                  
        With .Font
            .ColorIndex = 10
        End With
        .NumberFormat = ChrW(9650) & " #,## 0.00"
    End With
    With target.FormatConditions _
            .Add(xlCellValue, xlLess, "=0")
                  
        With .Font
            .ColorIndex = 3
        End With
        .NumberFormat = "#,## 0.00;" & ChrW(9660) & "#,## 0.00"
    End With
    With target.FormatConditions _
            .Add(xlCellValue, xlEqual, "=")
                   .NumberFormat = "#,##0.00"
        With .Font
            .ColorIndex = 2
        End With
    End With
    End Sub

     

    but how can I pass the Selection to this function?

    Change_Condition (Selection)

    That gave me an error: '438' Object doesn't support this property or method

     

    Thank you


    adrian socolescu
    • Marked as answer by ucsel Tuesday, October 5, 2010 3:37 PM
    Tuesday, October 5, 2010 6:40 AM
  • Change_Condition (Selection.Cells)

     

    of course! :)

     

    Best regards


    adrian socolescu
    Tuesday, October 5, 2010 6:46 AM
  • 'but how can I pass the Selection to this function?

    Lose the parens:

    Sub test()
    Change_Condition Selection

    End Sub

    And you had one selection still in the macro:

    Sub Change_Condition(target As Range)
    target.FormatConditions.Delete
    With target.FormatConditions.Add(xlCellValue, xlEqual, "=0")
        .NumberFormat = "#,## 0.00"
        .Font.ColorIndex = 1
    End With
    With target.FormatConditions.Add(xlCellValue, xlGreater, "=0")
        .Font.ColorIndex = 10
        .NumberFormat = ChrW(9650) & " #,## 0.00"
    End With
    With target.FormatConditions.Add(xlCellValue, xlLess, "=0")
         .Font.ColorIndex = 3
        .NumberFormat = "#,## 0.00;" & ChrW(9660) & "#,## 0.00"
    End With
    With target.FormatConditions.Add(xlCellValue, xlEqual, "=")
        .NumberFormat = "#,##0.00"
        .Font.ColorIndex = 2
    End With
    End Sub

    • Marked as answer by ucsel Tuesday, October 5, 2010 3:37 PM
    Tuesday, October 5, 2010 3:03 PM