Conditional Format multiple cells

[Sundog]

Edit: corrected cell references.

My challenge is to format a list of cells based on values in an adjacent list. Example: Format B2 green if B2>A2, format B2 red if B2<A2. Do this for B2:B11. (No format if B2=A2 is fine.)

breakout.PNG

Edit: If I try to copy the format in B2 to B3:B11, doesn't work since B2 conditional format looks at value of =A2, not the formula =A2. So B3 looks for the value displayed in A2, not what's in A3.

[HansV]

Select B2:B11.
B2 should be the active cell in the selection.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first dropdown set to 'Cell Value'.
Select 'greater than' from the second dropdown.
Enter =B1 in the box next to it.
Click Format...
Activate the Fill tab.
Select green.
Click OK, then click OK again.

Repeat these steps, but this time with 'less than' from the second dropdown, and red as fill color.

[Sundog]

See my edit, sorry for the confusion and incorrect initial entries.

[HansV]

Select B2:B11.
B2 should be the active cell in the selection.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first dropdown set to 'Cell Value'.
Select 'greater than' from the second dropdown.
Enter =A2 in the box next to it. Make sure that it's NOT =$A$2
Click Format...
Activate the Fill tab.
Select green.
Click OK, then click OK again.

Repeat these steps, but this time with 'less than' from the second dropdown, and red as fill color.

[Sundog]

Here is what I see after I try it on C19:D24. Starting from D19:D24 highlighted, D19 active cell.

[attachment=0]b2.PNG[/attachment}

Looks right, but if I change d24 to >c24 by entering a new number larger than c24, color stays red.

[Sundog]

My problem. Entered c19, s/b =c19.

Fixed. Thanks, Hans.

This will replace twenty Conditional Formatting rules a day!

[HansV]

If you look closely, c19 is enclosed in quotes. This makes me suspect that you entered c19 in the box instead of =C19
It should look like this:

S1216.png

S1217.png

[Sundog]

Correctamudo.

[ChrisGreaves]

My challenge is to format a list of cells based on values in an adjacent list.

Hi Sundog. If you would like an example, download the file http://www.chrisgreaves.com/Downloads/Stats.xls from my web site.
I use the date field (column A) to alert myself when I am falling behind my self-imposed schedule.

I think that I am falling behind schedule when I discover a track that has NOT been recorded by close to its scheduled date.
If you load the workbook over a period of three or four consecutive days without changing the data, the number of red-font cells should increase.
Cheers
Chris

[CraigS26]

Student-Hm Office 2016
Any idea why Mine doesn't respect B > A2 = Green?

[StuartR]

Student-Hm Office 2016
Any idea why Mine doesn't respect B > A2 = Green?

Because you have specified ="A2" instead of =A2, so it is comparing B to the literal string "A2" instead of to the value in the cell A2

[CraigS26]

Thanks, Stuart. I noticed the quotes and failed to mention them.
So default entry of A2 (App puts = & " ") is an auto-error in this case. Whodathunkit.
All is well.

[HansV]

You have to enter it in the form =A2. If you just enter A2, Excel converts it to a quoted string as you have found.

[CraigS26]

Thanks, Hans. I'll make an appt with an ADD doctor as your point about = is all over this thread....