visual basic conditional formatting produces strange results

C

Carl

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vba conditional formatting and relative references 2
conditional formatting and variable range sizes 0
Conditional Formatting 5
VBA adding Conditional Format to Range referencing another column value 3
Conditional Formatting Issue 5
Conditional Formatting in 2007 1
Conditional Formatting Strangeness 7
Help with the following code...from EntireRow to set width 3

Top