CODEWITHSUNDEEP
excel
Jrules80
Jrules80

Reputation: 178

Excel: Conditional Format specific cells that match rule

Need help with Conditional formatting Rule

In the example below, I only want to highlight the specific cell that passes the rule - E1 > 100% So, only E3 needs to be highlighted in Red. With the current rule, it is highlighting every cell from E4 thru E13. I only want E3 to highlight because E3 passed the criteria. And similarly for other matching E cells.

enter image description here enter image description here

I have modified the formula per "UrDearBoy" and this is how it looks now and it works!

enter image description here

Upvotes: 0

Views: 197

Answers (3)

urdearboy
urdearboy

Reputation: 14590

You need to amend rule to ignore text values so change your formula to:

=AND(ISNUMBER(E1),E1>100%)

FYI - Conditional formatting can be processor heavy so I would consider limiting the Applies To range to the relevant range rather running this check on all ~1MIL rows

enter image description here

Upvotes: 1

Michael Wycisk
Michael Wycisk

Reputation: 1695

If you want to go with a simple formula based solution, you can use:

=N(E1)>100%

The N() function converts text to 0 and numbers to their numerical value.

Upvotes: 1

mgrollins
mgrollins

Reputation: 651

I was able to set up a rule with these properties by using "Conditional Formatting > Highlight Cell Rules > Greater Than" and then set the value to 1 as in the below screenshots.

Rule Description

Rules Manager Example

Upvotes: 1

Related Questions