Applying conditional formatting via VBA (XL2007)

[AOB]

Hi guys,

I'm trying to programmatically add some conditional formatting to a defined range. There are three rules I need to apply; I can add the first two but struggling with the last one.

The range is columns K through T

The rules should be as follows :

• If the value in column J is "Original", and all of the cells in K:T are blank, then colour those cells grey
• If the value in column J is "Original", and the value in the cell directly below is different, colour that cell pale red
• If the value in column J is "Update", and the value in the cell directly above is different, colour that cell pale red

Here is the code that I have used to get the first two rules in place. I can't figure out the formula to use for the third one :

With sht

    Set rngCF = .UsedRange.Offset(, 10).Resize(, 10).EntireColumn

    With rngCF

        .Resize(1, 1).Select

        .FormatConditions.Add 2, Formula1:="=AND($J1=""Original"",COUNTA($K1:$T1)=0)"

        With .FormatConditions(.FormatConditions.Count)
            .Interior.Color = RGB(211, 211, 211)
            .StopIfTrue = True
        End With

        .FormatConditions.Add 2, Formula1:="=AND($J1=""Original"",K1<>K2)"

        With .FormatConditions(.FormatConditions.Count)
            .Interior.Color = RGB(255, 228, 225)
            .StopIfTrue = True
        End With

    End With

End With

 

[AOB]

Okay I've figured out a solution - not entirely sure how *good* it is but it works, which isn't a bad starter I guess. Posting here in case anybody else is having a similar issue...

So basically, rather than writing the formula to compare the cell with the cell immediately above (which logically is what I want, but difficult to incorporate into a generic formula using relative cell references), I compare the cell value with the cell which is X rows below, where X is the total number of available rows on the worksheet

(i.e. for Excel 2003, X would be 2¹⁶ or 65,536 rows, for Excel 2007 X would be 2²⁰or 1,048,576 rows etc.)

The idea being that, as a rough explanation, the relative reference goes all the way down to the bottom of the sheet, runs out of rows, then starts again at the top - making it's way to the cell immediately above the cell being compared (which was what I wanted originally)

Can't vouch that this is accurate as a technical explanation but I can vouch that I've tried it and it works.

Here is the code :

With sht

    Set rngCF = .UsedRange.Offset(, 10).Resize(, 10).EntireColumn

    With rngCF

        .Resize(1, 1).Select

        .FormatConditions.Add 2, Formula1:="=AND($J1=""Original"",COUNTA($K1:$T1)=0)"

        With .FormatConditions(.FormatConditions.Count)
            .Interior.Color = RGB(211, 211, 211)
            .StopIfTrue = True
        End With

        .FormatConditions.Add 2, Formula1:="=AND($J1=""Original"",K1<>K2)"

        With .FormatConditions(.FormatConditions.Count)
            .Interior.Color = RGB(255, 228, 225)
            .StopIfTrue = True
        End With

 [COLOR=#0000FF]       .FormatConditions.Add 2, Formula1:="=AND($J1=""Update"",COUNTA($K" & .Parent.Rows.Count & ":$T" & .Parent.Rows.Count & ")>0,K1<>K" & .Parent.Rows.Count & ")"

        With .FormatConditions(.FormatConditions.Count)
            .Interior.Color = RGB(255, 228, 225)
            .StopIfTrue = True
        End With[/COLOR]

    End With

End With

 

[RoryA]

You could use:

Formula1:="=AND($J1=""Original"",K1<>INDIRECT("R[-1]C",0))"

 

[AOB]

Could've done with that a month ago Rory! I've been wracking my brains trying to figure it out!

Yours is obviously a considerably more elegent solution (I really like it); is there anything wrong with what I came up with?

 

[RoryA]

No, that should work too.