vba conditional formatting and relative references

C

Carl

Maybe I'm just being a bit dense, but I hope someone can help me on this one.
I'm trying to set up conditional formatting on some cells in a worksheet so
that the cell with the formatting applied to it is set to specific colors
depending on the cells below it. However, the number of rows below will vary
down the sheet, and the user wants to manually add in rows, so the original
formula I was using:
..FormatConditions.Add Type:=xlExpression, _
Formula1:="=and(COUNTBLANK(R" & sectionrow + 1 & "C" & colval & ":R" &
rowval & _
"C" & colval & ")=0,countif(R" & sectionrow + 1 & "C" & colval & ":R" &
rowval _
& "C" & colval & ","">"" & R" & sectionrow & "C" & colval & ")<>0)"
won't work as it puts in relative references.
Can anyone show me a workaround please?

Many thanks.
 
P

Patrick Smith [MSFT]

If your issue is that a releative reference is returned because of the way
you are building your string and it is automatically assigned to a range,
then place the string into a range object and use the Address method to
return it's address with the rowAbsolute and ColumnAbsolute parameters set
to True.

Hope this helps,
 
C

Carl

That did it - many thanks Patrick

Patrick Smith said:
If your issue is that a releative reference is returned because of the way
you are building your string and it is automatically assigned to a range,
then place the string into a range object and use the Address method to
return it's address with the rowAbsolute and ColumnAbsolute parameters set
to True.

Hope this helps,
Click to expand...
 
Top