visual basic conditional formatting produces strange results

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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

"Bob Phillips" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Carl" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob - it was the SELECT that fixed it!
> Nice one!
>
> "Bob Phillips" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >