Conditional formatting - colors

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:20 PM GMT

Question

Can I not use conditional formatting for color changes in cell if values are displayed in cell as 70% or E.g 30 sec E.g. Cell A1 is 99% and Cell B1 if >=A1 it should be green, If B1<A1 it should be red Cell A2 is 35 Sec. Cell B2 if <A2 it should be green, If B2>A2 it should be red

A1 and A 2 are hardcoded in cell but B1, B2 comes from a access app as a percentage value(like 98%) or a sec value(Like 45 sec). Conditional formatting did not work for me, I used as Formula Is : =B1>=$A1 format to green Formula Is : =B1<$A1 format to Red

Answer

A1 and A2 worked??,

for what I'm reading there might be a problem with the way that query from access is flowing through your file

if the hardcoded ones worked, then that is the answer, and you might need to use an additional formula to turn those "sec" into values, remember if you have written in a cell "45 seconds" it might be taking that as a text, if that's so use =value(B1) formula to convert them into value, then it should work smoothly

now, another catch could be that you may need to remove the text "sec" from those values. For example: assuming B1 as the text cell: for "24 Sec" =LEFT(B1,SEARCH(" ",B1)-1) for 98% =LEFT(B1,2)/100

another useful tips is that sometime those imported cells comes with spaces, you may not be seeing them, but they are there. for that use: =TRIM(B1)



Question

Can I not use conditional formatting for color changes in cell if values are displayed in cell as 70% or E.g 30 sec E.g. Cell A1 is 99% and Cell B1 if >=A1 it should be green, If B1<A1 it should be red Cell A2 is 35 Sec. Cell B2 if <A2 it should be green, If B2>A2 it should be red

A1 and A 2 are hardcoded in cell but B1, B2 comes from a access app as a percentage value(like 98%) or a sec value(Like 45 sec). Conditional formatting did not work for me, I used as Formula Is : =B1>=$A1 format to green Formula Is : =B1<$A1 format to Red

Answer

Yes, you can use that. What you posted that you are using, works perfectly for me. But without the dollar sign. You don't need a dollar sign.

I'm guessing it has to do with the data in A1, especially considering you mentioned it comes from an access app.

Can you email a sample to ipisors at yahoo dot com

Advertisement

©2022 eLuminary LLC. All rights reserved.