Conditional Formatting using VBA

[eoinymc]

Hi,

I am trying to do conditional formatting within a macro because the range changes each month when I run it...

However, it is throwing up a confusing issue...Here is the code

[/COLOR]Dim T1 As Worksheet, _        Upd As Worksheet
    Set T1 = Sheets("Table 1")
    Set Upd = Sheets("Update")
    
FRT1S = T1.Cells(Rows.Count, 2).End(xlUp).Row


Dim rng1 As Range
    Set rng1 = T1.Range(Cells(8, 2), Cells(FRT1S, 18))
    
    With rng1.FormatConditions
        .Delete
        .Add Type:=xlExpression, Formula1:="=$S8=$Z$6"
    End With
    
    With rng1.FormatConditions(1)
        With .Font
            .Bold = True
            .ColorIndex = 2
        End With
        With .Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.399945066682943
        End With
        With .Borders
            .ThemeColor = 5
            .TintAndShade = 0
            .Weight = xlThin
        End With 

    End With[COLOR=#333333]

So, when I run the code and look at the conditional formatting on the sheet, instead of seeing =$S8=$Z$6, I see =$S9=$Z$6 and it highlights the row above the row I'm looking for?!

Any idea why this is?

I'm using Excel 2007. I got a colleague to run it and he is using Excel 2010 and it seems to work fine for him...it's quite annoying and has been perplexing me for a number of hours now!!!!

Any help will be greatly appreciated.

Cheers,

E

 

[brans1982]

Have you tried putting the conditional format in a loop to apply the formatting to each row individually?

You should have to do this but it might solve the problem until someone can think of a better solution.

 

[eoinymc]

No that didn't work....but thanks for your reply!

 

[eoinymc]

FYI: I found this which fixes the problem -
The conditional formatting may be set incorrectly when you use VBA in Excel