Conditional Formatting

[Ark68]

I struggling with a basic function of Excel ... conditional formatting.
I simply wish to apply conditional formatting to the cells of column M.
Two Rules ...
If the value in $M(row) > 10.5 then font colour is green
If the value in $M(row) < 10.5 then font colour is red

I click on the column tot highlight the cells.
Conditional Formatting > New Rule > Use a formula to determine which cells to format

For rule 1, I type >10.5, and set the format (green font). I press OK, expectingh the first value of 14.577 in M3 would turn green. It didn't.
For rule 2, I type <10.5, and set the format (red font). I press OK, expecting the the second value of 8.2 in M4 to turn red. It didn't.

When I refer to manage rules, I get this info ...

Rule (applied in order shown)
Formula: ="<10.5" applies to =$M:$M
Formula: =">10.5" applies to =$M:$M

I'm not quite understanding where I'm going wrong, I suspect in how I enter the formula.

 

[Cubist]

Your syntax is wrong but there are built-in capabilities for those already.

 

[coulombevin]

I struggling with a basic function of Excel ... conditional formatting.
I simply wish to apply conditional formatting to the cells of column M.
Two Rules ...
If the value in $M(row) > 10.5 then font colour is green
If the value in $M(row) < 10.5 then font colour is red

I click on the column tot highlight the cells.
Conditional Formatting > New Rule > Use a formula to determine which cells to format

For rule 1, I type >10.5, and set the format (green font). I press OK, expectingh the first value of 14.577 in M3 would turn green. It didn't.
For rule 2, I type <10.5, and set the format (red font). I press OK, expecting the the second value of 8.2 in M4 to turn red. It didn't.

When I refer to manage rules, I get this info ...

Rule (applied in order shown)
Formula: ="<10.5" applies to =$M:$M
Formula: =">10.5" applies to =$M:$M

I'm not quite understanding where I'm going wrong, I suspect in how I enter the formula.

Hi Ark68,

Cubist is right, you should use functions already setup for that. Althought, if you realy want to use a formula, you need to keep in mind that you need to reference a cell/range and unlock the row or the column or both depending on your purpose. In your case, select the column M and the formula you put in conditional formating should look like this:

=M1<10.5

If by any chance you want to highlight a part of your table based on one column like so, and prevent coloring if the cell is empty you can select all the column you want and in your formula, lock the column with $:

=AND($M1<>"",$M1<10.5)

Classeur1
	G	H	I	J	K	L	M	N
1		t	e	s	t	1	10
2		t	e	s	t	2	20
3
Feuil1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H:M	Expression	=ET($M1<>"";$M1>10,5)	text	NO
H:M	Expression	=ET($M1<>"";$M1<10,5)	text	NO

Also, in your case nothing happend if = 10.5. Have you thought about >=10.5 or <=10.5?

Bests regards,

Vincent

 

[Ark68]

My apologies for not acknowledging your contributions more promptly.
Thank you very much for sharing your knowledge. @Cubist, clearly I was making things more complicated than I needed. I do recall looking at that, but was thrown off having overlooked the ability to customize the formatting.
Vincent, thanks so much for the lesson, I found it very helpful in my understanding of how CF works.