Conditional Formatting - highlight cells where formula is different?

2
votes

Is there a way via Conditional Formatting (preferably no VBA, but if it's a must, then I'm open to it), to highlight a cell where the formula "idea" is different than the cell above?

I have a column of formulas, but have to manually edit a few of those. I'd like to have those manually edited formulas highlighted, so when I change the formula for the other cells, I know which cell to skip when updating that column.

For example, here's my column and formulas:
enter image description here

I'd like to have B5 highlighted yellow, since the formula is different.

I've tried using =FORMULATEXT($B3)<>FORMULATEXT($B2) but that doesn't work, since it's looking at the literal formula text...in which has they're always different. (=FORMULATEXT(B3)=FORMULATEXT(B2) will always be FALSE since the formula is technically changing, despite it being the same "idea").

I could also perhaps use =LEN($B3)<>LEN($B2) but that would have a false positive when the row changes from 9to 10, and again from 99 to 100...

The other option would, of course, just be to work in an IF()statement to clarify why I'm doing a different formula, i.e. =IF(ROW()=5,A5+A4+A2+A1,A5+A4) and use that...but there's no real logic for why I have to edit manually I could work in - which is why I'd just like a nice visual reminder on those random cells that the formula isn't like the others.

Edit: Quick note that the above formulas are way simplified. My actual ones are a little complex. I'm looking for a general answer to this too. Just thinking for my purposes, I could maybe do a check that if the formula has more than two + in it, highlight the cell. ...but I'm interested in a general way to solve this type of issue that could apply more broadly.

2
excelexcel-formulaconditional-formatting
So the little green triangle in the upper left corner is not sufficient? 8P – Scott Craner
You could use more generic formulas: =INDEX(A:A,ROW())+INDEX(A:A,ROW()-1) then you can compare the FORMULATEXT directly. – Scott Craner
@ScottCraner - Haha, that's true. While yes, that would work, it's a large spreadsheet, and it's zoomed out to 55%, so it's pretty easy to miss the green. I've also added a comment already, but would just like something more obvious. Also, the formula here is way simplified...it's really a VLOOKUP(), COUNTIFS(), with some IFERROR() and the "manual" one includes an additional COUNTIFS() or two. – BruceWayne
@pnuts - Great minds think alike - I've actually added a comment. But as I noted, the page is zoomed out a lot so it'd be easy to miss when dragging my updated "regular" formula down. Perhaps a way to check if a cell has a comment, then highlight? Hmm.... – BruceWayne
If you set formulas to R1C1 style (Options/Working with formulas) then you will at least remove the differences caused by copying between rows and columns. You'd probably want to toggle this back after seeing the effect though! – Lord Peter

2 Answers

2
votes

Here's another option for UDF:

Function findDifferent(Rng As Range) As Boolean

findDifferent = Not (Rng.FormulaR1C1 = Rng.Offset(-1).FormulaR1C1 Or Rng.FormulaR1C1 = Rng.Offset(1).FormulaR1C1)
End Function

enter image description here

1
votes

Here's a quick VB aided solution I came up with. If I add a comment to the special cells (which I do to explain the formula/why it's different), I can check for a comment then highlight it.

Add this function to the workbook:

Function has_Comment(cel As Range) As Boolean
has_Comment = False
If cel.Comment.Text <> "" Then
    has_Comment = True
End If
End Function

Then a simple Conditional Formatting formula of:

=has_comment(B2)

That works, and is relatively simple.

Edit: I found also you can do this, which doesn't rely on a comment. Just points out an Inconsistency Error.

Function has_inconsistency(cel As Range) As Boolean
has_inconsistency = False
If cel.Errors.Item(xlInconsistentFormula).Value = True Then
    has_inconsistency = True
End If
End Function