How to program conditional format dynamically

05-14-2008, 02:50 PM

jzd

Newcomer

Join Date: May 2008

Posts: 1

How to program conditional format dynamically

Hi,
I would like to be able to program the Formula in VBA dynamically but not sure how to make the .add function accept a dynamic string. E.g.

for each cell in range("a1:a10")
' i'd like to set the formula to not equal to the next cell, e.g.
' for cell A1, Formula = B1
' for cell A2, formula = B2, etc

FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual,Formula1:=???

next cell

VBA doesn't allow a string that's not a proper field name to be passed.
Any suggestions? Thanks much!

Edit by Moderator:
Please post Excel questions, in the Excel forum.

Thank you.

Last edited by Timbo; 05-15-2008 at 02:31 AM.

05-14-2008, 08:30 PM

Cas

Senior Contributor

* Expert *

Join Date: May 2008

Posts: 1,012

Hi jzd, welcome to the forum!

The formula string to pass must be of the form "=B1" if you want it to refer to the value in cell B1, as opposed to the literal string "B1". So what you need to do in your approach is to

navigate to the next cell, e.g. using .Offset
get its address, using .Address
prepend a "=" to that string

There is actually an easier way to do this, though - you can use the INDIRECT worksheet function with a relative R1C1 cell reference, e.g. the formula

Code:

=INDIRECT("R[-1]C",FALSE)

will set a cell equal to the value of the cell above. And since this uses relative coordinates, you can copy the formula to another cell and it'll refer to the cell above that one, without the need for updating the reference. Using that, you can set the conditional formatting for the entire range without the need to loop through the cells.

Hope I didn't misunderstand what you want to do.

05-15-2008, 01:53 AM

Colin Legg

Out Of Office

Retired Moderator
* Expert *

Join Date: Mar 2005

Location: London, UK

Posts: 3,398

Hi jzd and welcome to the forum!

Please be sure to read the posting guidelines.

You can just directly do it like this without looping and without copying:

Code:

Range("A1:A10").FormatConditions.Add _
                        Type:=xlCellValue, _
                        Operator:=xlEqual, _
                        Formula1:="=$B1"

I obtained it just by tidying up the code produced by the macro recorder.
Try it with Not Equal and see what you get.

Colin

__________________
RAD Excel Blog