Conditional formatting via VBA

[mcs51mc]

Hi there,
I have 2 questions regarding conditional formatting via VBA with Excel 2003

With this code one can add a conditional format to cell A1

With .Range("A1")
     .FormatConditions.Delete
     .FormatConditions.Add Type:=xlExpression, Formula1:="=IsError(A1)"
     .FormatConditions(1).Font.ColorIndex = 2
End With

But what if I want to apply this to a range that's changing all the time.
I mean the first pass is e.g. range R3 to R100, the next pass is T3 to T53, and so on...

I tried with "Formula1R1C1" instead of "Formula1" and then a range via variables, just like in cells, but it didn't work


--------------------------------------------------------------------------------------
The second problem is the "IsError" in the formula.
It works on a UK/US Excel but not on a Dutch version. In that case I have to use "IsFout".
UK formulas can be read by Dutch Exel but not conditional formatting ?!?!
Is this right, or do I do something wrong??


Thanks for any suggestion

 

[Jonmo1]

How do you determine which range you want it applied to?


This is a start, at least for doing a range

For Each c In Range("R3:R100")
    With c
         .FormatConditions.Delete
         .FormatConditions.Add Type:=xlExpression, Formula1:="=IsError(" & c.Address & ")"
         .FormatConditions(1).Font.ColorIndex = 2
    End With
Next c

 

[mcs51mc]

Thanks for your reply!

That subroutine gets the range as row and column offset from the caller.
But that range also got a name in the caller.
So I'll try with

For Each c In Range(myRange)

in the subroutine, where myRange is provided by the caller instead of Row & Column offset.
But looping through all the cells, won't that take a lot of time?
I have to process up to 60 ranges of around 100 cells in each of them


Time is no issue!! Great, thanks for this one!

Any idea about the language problem???

 

[Jonmo1]

You can do it all in one go, instead of looping each cell in the range..

Try

With Range("R3:R100")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=isnumber(RC)"
    .FormatConditions(1).Font.ColorIndex = 2
End With

 

[Norie]

What exactly is the language problem?

Is it when you try to use the Dutch version of IsError in the code?

 

[mcs51mc]

The language problem is like this:
I develop on a UK version of Excel, programmed ... Formula1:="=IsError(" & c.Address & ")"
On my PC the cells with error got a white font color, so one can't see the error text.

I then deploy the code on a PC with a Dutch version of Excel, no errors from the code but the cells didn't turn white. I then changed the "IsError" to "IsFout" and the font color of the cells was white again.

After searching the net & MS I found lots of stuff about language but nothing like "Use this syntax and it will work for all languages"

So I wonder how you guys deal with this issue.

For now I use (trial code for only 1 cel)

Select Case Application.LanguageSettings.LanguageID(msoLanguageIDUI)
    Case msoLanguageIDEnglishUS, msoLanguageIDEnglishUK
        .FormatConditions.Add Type:=xlExpression, Formula1:="=IsError(A1)"
        .FormatConditions(1).Font.ColorIndex = 2
                    
    Case msoLanguageIDBelgianDutch, msoLanguageIDDutch
        .FormatConditions.Add Type:=xlExpression, Formula1:="=IsFout(A1)"
        .FormatConditions(1).Font.ColorIndex = 2
                    
    Case Else
        'Other language, do nothing
                        
End Select

But this is not universal, I'm pretty sure there's a better way out there... ...


@jonmo1: I'll give it a try, thanks again!!