Formatconditions for dynamic range

[Ken Puls]

Hey guys,

Having a bit of an issue trying to set a conditional format via code for a group of cells. What I'm trying to do is have the entire row highlight grey if column C contains an #N/A error.

I've got this so far:

.Cells.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ISNA($C1)"

There doesn't seem to be an R1C1 method for this that I've found yet though...

Any ideas?

[mdmackillop]

How about a WorkSheet Change event

If Not Columns(3).EntireColumn.Find(What:="#N/A", LookIn:=xlValues, _
 SearchDirection:=xlNext) Is Nothing Then
Columns(3).EntireColumn.Interior.ColorIndex = 15
Else
Columns(3).EntireColumn.Interior.ColorIndex = xlNone
End If

[Ken Puls]

Hey Malcolm,

Thanks. I've never had much success using the Find method... somthing about it just doesn't like me.

A different path just occured to me, though! I hit it with an autofilter then coloured the visible cells using the specialcells method. Works a treat!

Thanks for the input though!

[Bob Phillips]

Ken,

So, what is the problem with the code?

[Ken Puls]

Hi Bob!

It sets
=ISNA($C1) for every cell. Doesn't change for each row. In row 2, I need it to be C2, etc...

No biggie, as I have a workaround, but something tells me I should be able to set a conditional format to do this by coding it.