FormatConditions in VBA in Excel? 1

[DD999]

I have two ranges, one call PNames and the other is Sch_Date.

PNames goes from A2:A10.
Sch_Data goes from D2:J10.

I'm trying to code a line in VBA that will look at the value in A2 (to see if it has the word Total in it) and if so will then conditionally format the cells in Sch_Data that are also in row A based on whether the value in Sch_Data = 100.

Here' the code that doesn't work:

Range("Sch_Data").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(Name,5)=""Total"", Sch_Data <>100)"

With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With

Can someone tell me what I'm doing wrong?

Thanks

 

[SkipVought]

Hi,

Problem 1: Range Sch_Data spanning multiple columns

Problem 2: Named Ranges in Cond Fmt

Solution:

 Formula1:= "=AND(RIGHT($A2,5)=""Total"",$D2<>100)"

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

oops

"=AND(LEFT($A8,5)="Total",$D8:$J8<>100)"

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[DD999]

Hi Skip,

I wish I could give you 5 stars for this. It was a simple thing but it's been driving me nuts and the "Definitive Guide" I bought must have this little tidbit buried in the middle somewhere.

I made one minor modification. Instead of $D8:$J8 I changed it to just D8. That way it checked every cell in the range and formatted them individually. With the $D8:$J8 none of the cells got formatted unless none of the range was equal to 100.

A neat trick to remember as I'm sure I'll need it again.

Thanks a ton!!!