Conitional Formating

[Excel Jr]

Hi,

I have a huge dataset and I need to apply conditional formatting in a very strange and complex way. The data is a mix of hard coded and calculated prices. I need a formula that will apply formatting only if the cell ends with an 5 in the second decimal place (20.55, 5.05, 42.95, etc.). I've tried a combination of round, if, countif with wildcards and I honestly don't understand intermediate conditional formatting, it's so basic compared to all the other features. VBA is a viable solution. I'd be greatful for any solution, but I can't go through and change the formulas on the sheet to round beforehand. It needs to be taken care of separately.

Thanks as Always!

 

[dreid1011]

I would have thought =RIGHT(A1,1)=5 would have worked, but to no avail. Anyway, perhaps this?

=ISODD(A1/.05)=TRUE

Edit: Hmm, nevermind, this will return true for values .05-.09.

Edit #2 : So, if anyone else reading, maybe you have some insight as to why =RIGHT(A1,1)=5 doesn't work? I tried =RIGHT(A1,1)<>5 and that worked... but not the other way around.

 

[dreid1011]

Annnnnd never mind, complete dumb dumb this morning.

Try this!

=RIGHT(A1,1)="5"

 

[Fluff]

How about
=RIGHT(ROUND(C2,2),1)*1=5

 

[Excel Jr]

You Sexy Man! I owe you a taco!

For real though, Thanks!

 

[dreid1011]

You are quite welcome

 

[Indystick]

If all your values are only taken out to two places then this formula (adjusted for whatever cell references you need) will work as a conditional formatting rule:

=NUMBERVALUE(RIGHT(E2,1))=5

If not, this works regardless of how many values before or after the decimal:

=NUMBERVALUE(RIGHT(MID(E10,SEARCH(".",E10),3),1))=5

The only reason I'd use these instead of the suggested rounding approach above is if I didn't want to round the values.

 

[Excel Jr]

Fluff, I should've said you spun me "right round" with that answer

 

[Fluff]

with comments like that I'm

Glad we could help & thanks for the feedback