Conditional Formatting Issue

[jhculbert]

What follows is a Mini-Sheet for a set of data I have, where the specific conditional formatting I want is not working. What I want is for Column F data to show a green background when the data are less than or equal to Column A data, and Red (it actually shows up as pink, as red does not seem to be a background color choice here) when Column F is greater than Column A. What is maddening is I have this same conditional formatting on another block of data in the same spreadsheet, and it is working correctly. I hope someone can help me here. Thanks!

Conditionap_Formatting.xlsx
	A	B	C	D	E	F
2	$92.29	$3.10	7	35.0	23.0	$96.19
3	$566.47	$11.49	13.1	50.0	29.9	$519.16
4	$80.74	$5.78	3.9	10.8	8.5	$57.95
5	$137.49	$103.84	14.0	30.0	28.8	$3,480.30
6	$177.51	$51.12	15.0	50.0	86.9	$4,024.04
7	$178.26	$5.62	9.6	25.8	19.6	$139.82
8	$50.93	$3.58	10.5	15.5	7.0	$44.50
9	$372.86	$26.93	12.0	12.0	8.4	$307.65
10	$79.09	$3.29	2.0	22.0	17.0	$65.44
11	$744.11	$11.63	9.7	35.0	27.2	$396.78
12	$122.77	$2.71	5.1	30.0	32.6	$89.15
13	$412.22	$8.94	12.7	30.0	39.5	$350.12
14	$152.51	$5.25	5.8	29.1	18.3	$131.64
15	$112.83	$7.47	5.6	15.0	14.8	$117.54
16	$66.35	$2.11	8.2	26.0	13.2	$44.75
17	$209.21	$8.01	10.9	23.0	10.0	$146.57
18	$134.67	$3.90	8.6	25.0	27.0	$110.12
19	$115.13	$8.60	10.0	15.0	7.4	$105.95
20	$429.29	$10.50	9.7	30.0	19.0	$282.20
21	$283.57	$5.21	14.0	32.0	24.5	$167.79
Sheet1

Cell Formulas
Range	Formula
F2:F21	F2	=(((D2+E2)/2)*B2*(1+(C2/100)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F21	Cell Value	<="Cell Value <= $A$2"	text	NO
F2:F21	Cell Value	>"Cell Value <= $A$2"	text	NO

 

[etaf]

you have "" around the formula
and not sure about the formula

you have = for both - only 1 needs the =

BUT TRY

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
F1:F100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND($F1<>"",$F1<=$A1)

Format [Number, Font, Border, Fill] - Fill Green
choose the format you would like to apply when the condition is true
OK >> OK

another rule
=$F1>$A1 = Fill RED

Not - I have selected from row 1 - and the formula starts in row 1
so whereever you start the selection to colour make sure the rows are same

so if it starts in row2 - select F2:F100 or howver many rows
and formula
=AND($F2<>"",$F2<=$A2)

Book3
	A	B	C	D	E	F
2	566.47	11.49	13.1	50	29.9	57.952303
3	80.74	5.78	3.9	10.8	8.5	3480.30144
4	137.49	103.84	14	30	28.8	4024.0386
5	177.51	51.12	15	50	86.9	139.821104
6	178.26	5.62	9.6	25.8	19.6	44.503875
7	50.93	3.58	10.5	15.5	7	307.64832
8	372.86	26.93	12	12	8.4	65.4381
9	79.09	3.29	2	22	17	396.777221
10	744.11	11.63	9.7	35	27.2	89.148973
11	122.77	2.71	5.1	30	32.6	350.119455
12	412.22	8.94	12.7	30	39.5	131.64165
13	152.51	5.25	5.8	29.1	18.3	117.535968
14	112.83	7.47	5.6	15	14.8	44.747192
15	66.35	2.11	8.2	26	13.2	146.570985
16	209.21	8.01	10.9	23	10	110.1204
17	134.67	3.9	8.6	25	27	105.952
18	115.13	8.6	10	15	7.4	282.20325
19	429.29	10.5	9.7	30	19	167.78805
20	283.57	5.21	14	32	24.5	0
21
22
23
24
25
Sheet1

Cell Formulas
Range	Formula
F2:F20	F2	=(((D3+E3)/2)*B3*(1+(C3/100)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F1:F100	Expression	=$F1>$A1	text	YES
F1:F100	Expression	=AND($F1<>"",$F1<=$A1)	text	YES

 

[jhculbert]

etaf - Thank you for your quick reply.
You provide alot of varying info. I need to absorb before I can try any of your suggestions.
I'm keeping my fingers crossed I can fix my issue using part of them, at least.

 

[etaf]

you are welcome
post back anything not understanding or not working

 

[jhculbert]

I have now gone through your instructions, and they are quite different than my for my Microsoft for iMac v. 16.83, Office Home & Student 2021. I tried to interpret what you are saying, but all cells in column F remain green, even though my formula set conditions on some becoming pink, when true.

I still need help on my issue. I could share the data section that does work, if that would help.

 

[etaf]

ok,
i'm also using 365 on a mac

a couple of images

where are you stuck

 

[jhculbert]

Well, thank you! Sorry for the delay in responding.
Once I changed the conditional formatting rule for the first row after reviewing your screenshots,
all of the other rows self-corrected.
I don't understand why that would be, but I'm happy it now works for this data section.

 

[etaf]

so all now solved for you