Conditional Formatting with Color does not work in every cell

[srootes1]

I've looked in other theads, but I have not been able to find an answer to my issue.

I am having an issue with my conditional formatting not working for every cell in my spreadsheet. Some cells it works fine, but others it doesn't and I cant figure out why.

What I need the conditional formatting to do:

• Display green when the cell value is equal to 100%
• Display orange when the cell value is between 95% and 99%
• Display red when the cell value is less than or equal to 94%

I am using the "Format only cells that contain" rule type.
The formatting is only applied to one column, but not every single cell (i.e. =$Z$4:$Z$16, $Z$19:$Z$31....etc.) (I did try changing the formula to cover the ENTIRE column, but that did change anything other than adding red wherever there was a blank space).

In this example, one cell is highlighed orange for 99%, but the cell below is not. Same with the 100% not changing to green.

I have three tabs with essentially the same layout. The conditional formatting does not work correctly in any of the tabs. I checked to see if the issue happened on the same row in each tab, but it does not.

Things that I have done to troubleshoot:

• Ensured the formulas within the cells were correct
• Set all cells to Percentage with no decimal places
• A bunch of other stuff I can't think of at the moment....

Any help is appreciated!!!

Here are some other screenshots...

 

[etaf]

any reason for not selecting an entire range of cells

i tend to use formulas for all rules -

is that because they are blank - you could add a forth rule

anyway

Book1
	A
1
2	10%
3	20%
4
5	40%
6	50%
7	97%
8	70%
9	80%
10	90%
11	100%
12	10%
13	20%
14	30%
15	98%
16	50%
17	60%
18
19	80%
20	90%
21	100%
22
23
24
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A21	Expression	=AND(A2>=0.95, A2<1)	text	YES
A2:A21	Expression	=AND(A2<>"",A2<0.95)	text	YES
A2:A21	Expression	=A2=1	text	YES

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[srootes1]

I've tried to install the Add-In and it does not have the same capabilities. And I am not able to send it to drop box or one drive due to the security settings at my work.

 

[etaf]

I've tried to install the Add-In and it does not have the same capabilities. And I am not able to send it to drop box or one drive due to the security settings at my work.

ok

so have you tried setting up the 3 rules i posted

why not a complete range

$Z$4:$Z$16, $Z$19:$Z$31

whats in
Z17 , Z18

 

[etaf]

i have just selected contiguous ranges

$A$2:$A$7,$A$9:$A$14,$A$20:$A$24

and used a rule

and just said - make RED for a rule
=AND(A2>=0.95, A2<1)

you can see it has not filled the colour on cells the range does not cover

Book1
	A
2
3	0.95
4	0.96
5	0.95
6	0.95
7	0.95
8	0.95
9	0.95
10	0.95
11	0.95
12	0.95
13	0.95
14	0.95
15	0.95
16	0.95
17	0.95
18	0.95
19	0.95
20	0.95
21	0.95
22	0.95
23	0.95
24	0.95
25	0.95
26	0.95
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7,A9:A14,A20:A24	Expression	=AND(A2>=0.95, A2<1)	text	NO