PDA

View Full Version : [SOLVED] Formatconditions for dynamic range



Ken Puls
08-26-2005, 01:46 PM
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
08-26-2005, 02:23 PM
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
08-26-2005, 03:39 PM
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
08-26-2005, 04:08 PM
Ken,

So, what is the problem with the code?

Ken Puls
08-26-2005, 04:12 PM
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. :yes