Conditional Formatting: If calaculation of adjacent cells = value, then Format

[Modify_inc]

I have the worst luck when trying to remember how to type formulas for Conditional Formatting. They have subtle differences from the same functions used elsewhere in Excel.

I'm trying to do a simple conditional formatting for a cell that has a number in it. The number is manually typed in, it is not a calculation. But I would like to verify that the correct value was entered. To do that, I just need to subtract the two left adjacent cells from each other. If the value equals the cell with the typed number, then apply the formatted condition. Seems simple enough with an If Statement, but I can't figure out the correct syntax it needs.

Obviously, this doesn't work, but I'm sure you can get the gist of what I'm trying to do

=If(K11 - J11 = L11, Format, Don't Format)

 

[Rick Rothstein]

I have the worst luck when trying to remember how to type formulas for Conditional Formatting. They have subtle differences from the same functions used elsewhere in Excel.

I'm trying to do a simple conditional formatting for a cell that has a number in it. The number is manually typed in, it is not a calculation. But I would like to verify that the correct value was entered. To do that, I just need to subtract the two left adjacent cells from each other. If the value equals the cell with the typed number, then apply the formatted condition. Seems simple enough with an If Statement, but I can't figure out the correct syntax it needs.

Obviously, this doesn't work, but I'm sure you can get the gist of what I'm trying to do

=If(K11 - J11 = L11, Format, Don't Format)

The formulas used for Conditional Formatting must return either a TRUE or FALSE value.

What cell or cells are you applying this Conditional Format to?

 

[Modify_inc]

I have the worst luck when trying to remember how to type formulas for Conditional Formatting. They have subtle differences from the same functions used elsewhere in Excel.

I'm trying to do a simple conditional formatting for a cell that has a number in it. The number is manually typed in, it is not a calculation. But I would like to verify that the correct value was entered. To do that, I just need to subtract the two left adjacent cells from each other. If the value equals the cell with the typed number, then apply the formatted condition. Seems simple enough with an If Statement, but I can't figure out the correct syntax it needs.

Obviously, this doesn't work, but I'm sure you can get the gist of what I'm trying to do

=If(K11 - J11 = L11, Format, Don't Format)

Ok, I figured part of it out, now I just need it to do the same thing for all the remaining cells in that column. For some reason it's not auto adjusting the cell values for the remaining columns.

This works for the formula, but it doesn't auto adjust the formula values for the remaining cells in the column that need to be checked. It's checking them all against this same cell value.

=K11-J11=L11

I figured it was the $ being used with the values in the "Applies to" field, but when I remove them and click Apply, they come back.

=$L$11:$L$35

 

[Modify_inc]

I just posted a reply while you were posting. I've got part of it working, but now can't get it to duplicate for the remaining cells in the columns. I mean, it duplicates, but the cell values need to auto adjust for each row, currently, it using the same cell values for the remaining cells in the column.

 

[Fluff]

With the formula you've supplied & the Applies to range, it should be working.
Whilst every cell may look as though its just using row 11 it's actually using the cells on it's own row.

This is what I get

Excel 2013/2016
	J	K	L
11	10	9	-1
12	9	10	1
13	5	10	6
14	5	10	5
ddr

 

[Modify_inc]

It is working!

It was my mistake. I forgot blank cells still equal blank cells. It just threw me off when I seen the formatting go all the way down the column. I just assumed it was still comparing the cells with the same original cell range of L11-J11, and every time I clicked to edit the Conditional Formatting, it always showed that same range, but apparently that is by design.

 

[Modify_inc]

Can I mark this thread as Solved or should I just leave it as it is?

 

[Fluff]

Glad it's working & thanks for the feedback.

We don't mark threads as solved here, so just leave as is.