[Solved] conditional formatting

Fluppe · Jul 15th 2003

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

**Andy Pope** · Jul 15th 2003

Hi Fluppe,

This minor change in order worked for me.

Code

With Selection.FormatConditions(1).Interior
            .ColorIndex = 0
            .PatternColorIndex = 15
            .Pattern = xlGray50
        End With

Cheers
Andy

Fluppe · Jul 15th 2003

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?

**Andy Pope** · Jul 15th 2003

Hi,

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

Code

c = c+1 
Next c

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

Cheers
Andy

Anonymous · Jul 15th 2003

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?

Fluppe · Jul 16th 2003

Hi,
See my attachement.
This is only the code for the conditional formatting I posted already.
The same problem occurs.

Thanx,
Fluppe

**Andy Pope** · Jul 16th 2003

Hi Fuppe.

Code generated by recording a macro

Code

With Range("A3:E3")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=ISNUMBER(A3)"
        With Selection.FormatConditions(1).Interior
            .ColorIndex = 0
            .PatternColorIndex = 15
            .Pattern = xlGray50
        End With
    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

Fluppe · Jul 16th 2003

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()

**Andy Pope** · Jul 16th 2003

Hi Fluppe,

Code to loop from 3 to 300 in steps of 3

Code

For lngrow = 3 To 300 Step 3
        Range("A" & CStr(lngrow) & ":E" & CStr(lngrow)).Select
        With Selection
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=ISNUMBER(A" & CStr(lngrow) & ")"
            With Selection.FormatConditions(1).Interior
                .ColorIndex = 0
                .PatternColorIndex = 15
                .Pattern = xlGray50
            End With
        End With
    Next

Display More

Cheers
Andy

Fluppe · Jul 16th 2003

Andy,
Now I can go on.
Tanx alot:biggrin:

[Solved] conditional formatting

Participate now!

Share