asked on

Conditional formating via VBA

Hi

I have a workbook in which i format some cells.

The value used in the cell is sorted out already using the following:

Range("DocIssue").Offset(1, 0).Formula = _
"= IF(AND(N6< TODAY(),L6=""""),""Overdue by "" & (TODAY()-N6) & "" Days "" & TEXT(N6,""dd/mm/yyyy""),TEXT(N6,""dd/mm/yyyy""))"

'INSERT CONDITIONAL FORMATING CODE HERE

With Sheets("Doc Register - Tracking Sheet")
Range("DocIssue").Offset(1, 0).AutoFill Destination:=Range("DocIssue").Offset(1, 0).Resize(RowTracking - StartRow, 1)
End With

However i also want to set conditional formating for the cells in column K.

I can set the formating up from the workbook but i want to set it in the code wher indicated.

The formating for the cells in column K is:
Condition 1
=IF(L6=""; LEFT(K6;7)="Overdue") Red Fill and Yellow text

Condition 2
=IF(L6=""; LEFT(K6;7)<>"Overdue") Light green fill and black text

Thanks

Gordon

patrickab

Gordon,

Here's an example file:

http://www.asdy88.dsl.pipex.com/Experts%20Exchange/gordonmasson04.xls

Let me know how you get on with it. I've not quoted the VBA here as it's boringly long from a recorded macro. However the syntax of the formula needs to be:

=OR($L$6="",LEFT($K$6,7)="Overdue")

if indeed that's what you wanted?

Patrick

GordonMasson

ASKER

Patrick

Thanks for the response.

I did actually try somthing like that and when i step through the code you have for Macro1 i get the same error which i was seeing on my code:

Run-time error 5
Invalide procedure call or argumet

Any ideas what the problem is here?

Thanks

Gordon

GordonMasson

ASKER

Sorry

should have said that the error occurs on this line of code

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR($L$6="""""""",LEFT($K$6,7)=""Overdue"")"

Thanks

Gordon

ASKER CERTIFIED SOLUTION

patrickab

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GordonMasson

ASKER

Patrick

Im still having problems with that line of code

this seems to work:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR($L$6="""""""";$K$6=""Overdue"")"
using a ; in place of a , and ignoring the LEFT part of $k$6 but the following line ... even with a ; in place still gives me an error

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR($L$6="""""""";LEFT($K$6,9)=""Overdue"")"

could this be somthing to do with the default settings in excel ???

Thanks

gordon

patrickab

Try making it into one line of code without the _ and using commas.

GordonMasson

ASKER

Tried that im afraid ... even when line is :

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=OR($L$6="""""""",$K$6=""Overdue"")"

i still get the same error

I am using Microsoft Office Excel 2003 SP2 from Microsoft Office Professional Edition.

ANy ideas ?

Thanks

Gordon

GordonMasson

ASKER

Patrick

I tried running this code on another PC last night with the same issue of XP and office 2003 and it worked fine but it hangs up on the line i highlighted above if i try to run this on my main pc.

Is there anything that you can think of that would be causing this to hapen?

Thanks

Gordon

patrickab

Try it like this:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=OR($L$6="",$K$6="Overdue")"

On one line

GordonMasson

ASKER

No joy

That gives me a compile error
Expected:end of statement

The text for Overdue is highlighted

patrickab

Try:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=OR($L$6="",$K$6=Overdue)"

GordonMasson

ASKER

Same problem ....

Run-time error 5
Invalide procedure call or argumet

I really think this is an issue with either Excel set up or perhaps even a default setting on the pc ??

Cheers

Gordon

GordonMasson

ASKER

Got it !!

The problem is to do with one of the control panel settings... not sure why this causes the problem but it does

From Control Panel
Reigional and Laqnguage Settings
Reigonal Options
Customize
List Seperator

This was set to ; changing it to , resolves the problem.

Thanks for your help anyway.

Cheers

Gordon

patrickab

Gordon - Pleased that you've cracked the problem. It had not occurred to me that a regional setting would have stopped the sub from running. Thanks for the grade. - Patrick