Conditional Formatting in VBA
 

hi all,

your help is mostly appreciated on following...

Note: the column D used below is only for example, when executed, the
code loops through each column of selection

...i use Conditional Formatting manually by adding a formula
(=SUM(IF($D$5:$D$9="",0,1))<0) into $D$4 with appropriate setting and
all is well - if either cell D5, D6, D7, D8 and/ or D9 has a value, D4
will be formatted as specified.

however if i use this same method in VBA, D4 is only formatted as
specified if D5 has a value, but D6, D7, D8 and/ or D9 are ignored. -
this is the code i use

Worksheets(sName) _
.Range("A1") _
.Offset(3, iColumn2) _
.FormatConditions.Delete
Worksheets(sName) _
.Range("A1") _
.Offset(3, iColumn2) _
.FormatConditions
.Add
Type:=xlExpression,
Formula1:= "=SUM(IF("
& Worksheets(sName). _
Range("A1").Offset(4,iColumn2).Address &
":"
& Worksheets(sName). _
Range("A1").Offset(8, iColumn2).Address &
"="""",0,1))<0"
Worksheets(sName) _
.Range("A1") _
.Offset(3, iColumn2) _
.FormatConditions(1) _
.Interior _
.Pattern = xlNone
With Worksheets(sName) _
.Range("A1") _
.Offset(3, iColumn2) _
.FormatConditions(1) _
.Font
.Bold = False
.Italic = False
.ColorIndex = xlAutomatic
End With

(the formula line is all one line)

thank you in advance for your help...

...jurgenC!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!