Spy Journal 3.0 Filtering the web for you

is it possible to do with words like home /away for fixture lists, i want all our home games to be say blue and our away one to be green?!??!

tonyshand@hotmail.com

thanx.. it was really helpful!! :)

Thank you so much! You really simplified this whereas alot of other resources I searched through made it sound quite complex. Thanks again!

Hi there, Thanks for this explaination it has helped a lot. You are probably tired of questions on this post now but hope you can help one more excel newbie. It all works fine, apart from the last line. I have a spreadsheet with percentages, depending on range from 0-100% a different colour is displayed. It works for all bands apart from 0. If i use the AND feature you have used then nothing changes, if i use =$H1<0.0001 or =0 as the last line then some blank sells are included but not all. Also bizzarely if i use either of these formulas some of the sells filled with '0' dont change! Hope you can help...can email the file if it is ok with you. Many Thanks Simon.

Following the example you gave, how about setting a color on a row based on values on column "H" and another column, such as "G"?

I want to make a formula that makes the row green according to the following conditions:
=$H1<1 AND =$G1>1

Translation: For all rows showing values that are less than 1 (or show empty cells) on column H, but that show values greater than 1 on column G, make the rows green.

I know I have to use a formula with AND, but can't figure out how to do it. Would you have any ideas?

Thanks

Hi Tim,
Thanks for having this site, I appreciate the time people spend helping others. I know this thread is old, but I just started in Excel 2007. I like the 3 color idea. I have a bunch of projects that I have to track. Usually about 7 days in length. I track the start, due and actual completed dates. I was able to highlight them in a gantt style worksheet and get a date range establish, with the start at on cell and the due at the other, the cells inbetween are yellow. I used =AND(I$5>=$D6,I$5<=$E6). I5 to AM 5 is my date values. D6 is the first start date, E6 is the first due date. I wanted to have it turn "green" when it was completed, that value would be F6. I also wanted to have it turn "red" if it went past the due date in E6. I know I need to add 2 additional rules but was not sure how to keep the date ranges. I still need to have the start and due dates intact, just have the colors change based on the due and complete dates. Any ideas would be great. Thanks again Tim.

hi,
in Excel 2007 I need to conditally format a row (fill with color) based on text rather than a numeric value. I have one worksheet and I need the rows highlighted with differernt color based on Approved,not approved, I can't seem to get the formula right to get the entire row highlighted. When I say "Approved " it only highlights that cell and not the entire row… Any suggestions pl let me know.

Hi jethro,

Your posts are really gold! I have a question about conditional formatting, can it work even if the value in the cell is a word? for example if the word ''Gone'' exists in any of the cells of ROW H, then the row should turn red. I tried inputing a new rule: as =$H1=''gone'' but this doesn't work while it works if i replace the word gone with a numerical value i.e. 3. Any ideas on how i can resolve this?

thanks
marina

Thank you! Have been stuck on this for a couple of hours - Microsoft Help no help at all - and following your instructions had it sorted within minutes.

Hi,

Can anyone help me. Not sure if this is possible and my head is now hurting trying to think of a way this will work. Formulas in Excel are not my strong point.

I have a spreadsheet with staff names in column A, and Training Course Titles in Row 1.

If I put a date into say cell B2, I would like cell B2 to change to red after 6 months have elapsed to highlight that that person needs to refresher training.

Can anyone tell me the formula I need to use (if it is possible)

Thanks so much

Clare :)

Hi Tim,

Thanks for this. Ok so when I put future dates in it works perfectly, unfortunately it turned my date red when it was only 2 days ago? Please can you advise whats wrong?

Many Thanks
Clare

Hi

my problem is more or less the same as above but a little more complicated, actually i dont know why the conditional formating isnt working, it would be better if i can send you the file of excel so that you see the problem, but heres some info.

I have 4 columns, and X rows, in them they have some formulas that based in a number and in a word (some IFs by the way) you get a number, so that i can do the conditional formating that you posted above, the thing is that i cant get the formating, and i dont know why, my best guess is that the conditional formating isnt getting the number but instead is reading the other formula, i did try to put a word instead of a number in the column that you did with the -1, 0, etc. but with a word it didnt worked. well it kind of does but just with the cell that have the word not the entire row.

I dont know if i made myself clear, thats why i kind of need to send you the worksheet, ill be waiting for your responce, thanks a lot!!

The initial problem is that i need to color the rows of my worksheet based in two things, A text and a Number. In cell A1 you have the Date 11/11/11 in cell B1 you have 11/15/11 in cell C1 you have a Word for example Pending and in cell D1 you have the number 4 (its the result from the formula =DATEDIF(A1,B1,"d"). The rules are the next ones:

from 0 - 5 with text Pending you have the row in yellow
from 5 to 999 with text pending the row turns to red
If it turns rejected in cell C1 it turns to red whatever number is in cell D1
If it turns accepted in cell C1 it turns to green whatever number is in cell D1
but if it turn to number 7 it goes red.

Thats the whole problem i tried a lot of things, i can even send you my worksheet to see what i did, or you can do something based in the rules and example that i just post, i dont know im kind of desperate because nothing works... thanks a lot for your help.

I have been trying to figure this out all night and maybe its just because I am not familiar enough with excel. I am trying to create a conditional formatting rule that I can easily copy to each individual column without having to rewrite the entire format. So kind of like duplicating a formula across a lot of columns. So in column A I want to highlight every number that is less than A2. But I want to duplicate this across all columns matching up to its own percentage B2, C2, D2. I can get one column to work with this formula =$A$3:$A$6<$A$2. Any Help would be great. Thanks

A B C D
76% 66% 76% 75%
67.83% 66.99% 74.27% 73.03%
66.92% 66.40% 74.35% 73.07%
66.79% 66.28% 74.27% 73.06%
66.76% 66.31% 74.34% 73.05%
66.94% 66.31% 74.23% 73.02%
67.02% 66.36% 74.25% 73.02%

You rock. Exactly what I was looking for. Not the most intuitive of features.

Thanks a lot, Bro!!!!!!!!!!

I have been struggling to use this condition on an if condition that returns Due or Not due. Looking at you example which 1 is that you have in $H1? Excel doesn't seem to allow me select the whole column. =$H1=1

Thanks this helps me lots

Day F S S M T W TH F S S M T W TH F
Date 24 25 26 27 28 29 30 31 1 2 3 4 5 6 7
john V V V V V V V V V V V V V
michle 8 0 0 0 0 4 8 0 0 0 0 0
tom 8 0 0 0 0 0 4 8 0 0 0 0 0
suresh 0 0 0 0 0 0 0 0 0 0 0 0 0
anthony 0 2 2 2 2 0

Please advise me how to make colour all fridays collum by using conditional formatting.

What I would really like to accomplish seems so easy but trial and error is mostly error. I have a header and column fields that are auto filled based on certain criteria. I am trying to make a universal form as a timesaver. Ultimately, from A13:Q14 through infinity, a name is generated in column A. Each instance of generation will have a different number of names. I would like the borders of B:Q to auto fill when any text appears in A13 and south. As close as I've come has been =$13=ISTEXT(A13). The issue here is the borders are prefilled and as I add text, they disappear.
Dave