I am looking to set up conditional formating on an excel model so that any cell that does not contain an "If" formula within a certain range is highlighted in a different color. I want to easily ve able to see where people have made manual adjustments to the model.

I am working on excel 2007

You can not use Conditional Formatting in the manner you'd like. A CF Rule must evaluate to either TRUE or FALSE, using standard Excel functions and/or operators.

Since there is no standard Excel function and/or operator to check whether a cell contains a specific type of formula, there's no way to set up CF rule to check whether a cell contains a specific type of formula.

You could do it with VBA.

For example, this code will check A1:A10 and fill any cell that does not contain an IF function with the color green.

Sub If_Not_If() For rw = 1 To 10 If Left(Cells(rw, "A").Formula, 3) <> "=IF" Then Cells(rw, "A").Interior.ColorIndex = 4 End If Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History