Conditional Formatting a Table by row if negative values

[TheRedCardinal]

Hi all,

I have a requirement to add conditional formatting to a whole table, such that if the value in a specified column in negative, the whole row is highlighted.

I have come up against two blocks:

1. My code doesn't actually work!
2. I can't work out how to make it factor in that the relevant column might move

Here is the code I've used:

Set FormRange = WS2.ListObjects(1).DataBodyRange

With FormRange.FormatConditions.Add(Type:=xlExpression, Formula1:="$I2<0")

    .Interior.Color = 65535
    .StopIfTrue = False

End With

It has populated the conditional formatting correctly:

But it doesn't take effect in the table itself.

Also, I have hard coded column I, but it might move - can I make the reference dynamic?

Thanks!

 

[etaf]

i dont rovide VBA solutions here
BUT the formula
$12<0 has "" around it - so that will not work
needs to just be
$12<0
not sure how you apply that in VBA - been over 20years since i used vba in any big way

duplicate values is only looking in 1 cell

 

[RobP]

Generally speaking, if you are searching (or wanting to search) for a value in a specified column that can move - a computer will have a hard time, unless there is something that is always (unique) in that column that will allow you to tell Excel to first find the column.

Might you be able to expand on that a little for us please ? (tricky for us right now as we can't see any of your data/table.)

thanks
Rob

 

[RoryA]

Your formula is missing an = sign: Formula1:="=$I2<0"