visual basic conditional formatting produces strange results

[Guest]

Hi, I'm trying to put together a piece of code that will generate conditional
formatting for cells in a worksheet. The formatting should alter the colour
of the cell based on values held in a range of cells beneath. In order for
the user to be able to add/delete rows in this range, R1C1 style won't work
as it doesn't give me relative references. Here's the code I've put together
as a test, but rather than generate formula for column A for the cells
immediately below the one formatting is to be applied to, it uses cell ranges
from other parts of the spreadsheet that I don't reference. Can anyone help
please?

Sub ColumnNo()

sectionrow = 2
colval = 1
rowval = 7
col_letter = "A"

Do Until sectionrow > 20

With Workbooks("create master board").Sheets("sheet3").Cells(sectionrow,
colval)

first_spread = sectionrow + 1

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=and(countblank(" & col_letter & first_spread & ":" &
col_letter & rowval & ")=0,countif(" _
& col_letter & first_spread & ":" & col_letter & rowval & ","">""& " &
col_letter & sectionrow & ")=0)"
.FormatConditions(1).Interior.ColorIndex = 35

End With

sectionrow = rowval + 1
rowval = sectionrow + 5

Loop
End Sub

 

[Bob Phillips]

Try this

Sub ColumnNo()

sectionrow = 2
colval = 1
rowval = 7
col_letter = "A"

Do Until sectionrow > 20

With Workbooks("create master board").Sheets("sheet3").Cells(sectionrow,
colval)

Cells(sectionrow, colval).Select
first_spread = sectionrow + 1

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=and(countblank(" & col_letter & first_spread & ":" &
col_letter & rowval & ")=0,countif(" _
& col_letter & first_spread & ":" & col_letter & rowval & ","">""& " &
col_letter & sectionrow & ")=0)"
.FormatConditions(1).Interior.ColorIndex = 35

End With

sectionrow = rowval + 1
rowval = sectionrow + 5

Loop
End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Bob - it was the SELECT that fixed it!
Nice one!

 

[Bob Phillips]

Yeah, it was using the activecell to offset the formula you created.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)