[Solved] conditional formatting

  • Hi,
    I’m using the following simple conditional formatting: when a cell contains a number it must change in a grey pattern.
    I’m using the macro below but it doesn’t apply the Pattern and the PatternColorIndex. The Colorindex works perfect. Does anybody know what is wrong in this code?


    For c = 1 to 5
    Range(Cells(c, 5), Cells(c, 7)).Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=ISNUMBER(E" & c & ")"
    Selection.FormatConditions(1).Font.ColorIndex = 55
    With Selection.FormatConditions(1).Interior
    .ColorIndex = 0
    .Pattern = xlGray50
    .PatternColorIndex = 15
    End With
    c = c+1
    Next c


    Thx,
    Fluppe

  • Hi Fluppe,


    This minor change in order worked for me.

    Code
    1. With Selection.FormatConditions(1).Interior
    2. .ColorIndex = 0
    3. .PatternColorIndex = 15
    4. .Pattern = xlGray50
    5. End With


    Cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]

  • Hi Andy,
    I have tried already to change the order
    but that doesn't help.
    I have tried the macro for formatting one cel and that worked. But when I adjusted the code for more than one cell it failed??
    Is it possible that it fails because I'm using it in a loop?

  • Hi,


    They only other thing I can see is that you are incrementing the loop counter within the loop.

    Code
    1. c = c+1
    2. Next c


    But this would only mean that every other row would have conditional formatting.


    Cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]

  • Without seeing your workbook to know what you are trying to do, you might change
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=ISNUMBER(E" & c & ")"
    to
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=ISNUMBER($E" & c & ")"
    This will keep the E from changing to F and G, but that may not be what you intended. Can you be more explicit about what fails now? Or better, post an example workbook?

  • Hi Fuppe.


    Code generated by recording a macro


    Code
    1. With Range("A3:E3")
    2. .FormatConditions.Delete
    3. .FormatConditions.Add Type:=xlExpression, Formula1:="=ISNUMBER(A3)"
    4. With Selection.FormatConditions(1).Interior
    5. .ColorIndex = 0
    6. .PatternColorIndex = 15
    7. .Pattern = xlGray50
    8. End With
    9. End With



    Your code was producing cell references
    A-1,A0,A1,A2,A3
    Also the two .Pattern lines needed to be swapped.


    Cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]

  • Hi Andy,
    Yes, that works perfect.
    I want it a little bit different but I forgot to mention it.
    I think I need a loop function because its not only range(“A3:E3”) that must have a conditional formatting but also range(“A6:E6”), range(“A9:E9”) and so on until range(“A300:E300”)
    I don’t know how I can do that?
    I tried to make something in sub Condformat2()

  • Hi Fluppe,


    Code to loop from 3 to 300 in steps of 3



    Cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]