Hopefully someone can assist:

I want to format a cell so, that if I insert a row below another row that is populated with data, and if the new cell is a different value than the cell directly above it the cell fills red.

I cant see how to do that. Its not a mathematical value, just a comparison. If they are the same, nothing, if they are different make it red so you can easily see where a new lot went into use. So I cant select "unique" because it wont be, it may repeat for several rows, then change.

So you may have:

123
123
123
145 - can this be red?
145
160 - red cell
etc....

Help?

I could do that with columns but I have no idea how to accomplish that with inserted rows.
I'd be interested in the solution as well.

Conditional formatting. Highlight cell rules. Equals. Change equals to to "not equal"

Select cell...then select "not equal" too...then select cell above.

Copy this down you column. Make sure to turn off the dollar signs so it references the cell above...and not the first, top cell for all rows.

You can do it by selecting the data starting with your first value, going to Conditional Formatting -> Highlight Cells Rules -> More Rules -> Use a formula to determine which cells to format.

Then use this formula:

=(F5<>INDIRECT(ADDRESS(ROW()-1,COLUMN())))*(ROW()<>ROW($F$5))

Replace F5 and $F$5 with the first data entry in your actual spreadsheet.

The part of the formula before the * checks if a value is different from the value above it, the part after ignores the first data cell (because it will always be different from the cell above it, because that cell isn't even a data cell).

Or you can select the entire column by clicking on the column label and use this formula the same way (replace the "F" in F1 with the column containing your data and the $F$5 with the cell containing the first data entry in the column):

=(F1<>INDIRECT(ADDRESS(ROW()-1,COLUMN())))*(ROW()>ROW($F$5))

(I just tested these and they do work as described.)

Both formulas multiply the two parts together rather than using AND() because in an array of cells, AND() only returns one value for the entire array, it doesn't work. Since Excel treats TRUE as 1 and FALSE as 0, multiplication is the same as AND except it works in an array context.

Can you add a hidden column to run the validation?

If your sample data started in A1

In B2, you could have A2=A1 which would result in a True/False

The have the conditional formatting flag off False values from column B, which is subsequently hidden from view.