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)

"Carl" wrote in message
...
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