I need one cell's formatting to change when another cell's value is "TRUE"

[laddersrich]

Hi,

I'm not sure if my problem requires an IF statement or conditional formatting or both!....

Basically I have already set up an IF statement on one column so that if the date in one cell ($D$1) is <3 days in the future, (so it only highlights the dates that are from today up to and including 3 days from the current date) then another cell ($G$1) is TRUE or FALSE. Now I want the cell to the left ($F$1) to be filled red if ($G$1) is TRUE. It can remain "not filled" if the result is FALSE.

I have tried just about everything I can and cannot find any similar issues on Google/forums,etc. Please help if you can! I would appreciate it if responses are specific to my needs and not just standard fixes, which I more than likely will have already tried.

Thanks in advance

[Jul Stev]

Now I want the cell to the left ($F$1) to be filled red if ($G$1) is TRUE. It can remain "not filled" if the result is FALSE.

based on that sentence, your need is a conditional formatting then.Try below step:
1. Choose cell F1
2. Go to home tab - conditional formatting - manage rules - new rule -Use a formula to determine which cells to format
3. put this formula : =G1=True
4. adjust the format accordingly

Hope it helps

[laddersrich]

OK that worked, but I can't seem to copy it to the rest of the column... even after adding the $'s! What am I doing wrong?!

Also, the cells I set up to return either TRUE or FALSE seem to return TRUE even when there is nothing in the cell it is looking at. How do I make one cell blank if the other one is too?

Thanks for your help.

[Sam Capricci]

Depends on a lot of things, one conditional formatting sometimes changes G1 to $G$1 and you may not want that to happen, you might want it to be $G1 so it locks in the column but not the row? You can use the format painter to quickly change a lot of the additional rows or columns to match your conditional formatting for one of them.

[EricDeeson]

As outcome of IF formula, Jul said "adjust the format accordingly". That's where I'm stuck - I'd like numerical cell contents to print in red if the value is negative. How, please, colleagues? (I've done it before, but forgotten how.)

Cheers - Eric

[laddersrich]

Awesome! So just putting the $ in front of the column and not the row, e.g. $G1 means I can copy and paste it to the rest of the column. Thanks!

Now all I need to know is how to make the other cell (returning TRUE/FALSE) to be left blank if there is nothing in the cell it is calculating?

Current statement in cell G3: =IF($D3<TODAY()+3,"TRUE"). So, if cell G3 (or any other cell in that column) has no date in it, I want it to remain blank as opposed to returning TRUE like it is currently...

[Sam Capricci]

are you sure your formula works? I tried it with several examples and always get true.

EDIT: ok, I see when it works. will try to get you a way to answer when blank in a sec.

[Sam Capricci]

Ok, change it to this =IF($D3="","",IF($D3<TODAY()+3,"TRUE"))

[laddersrich]

Works perfectly now. Much appreciated!

[laddersrich]

On a slightly different matter, I share this spreadsheet with other people and find that the IF statements and/or conditional formatting is either messed up or removed altogether. I think it's probably caused by copy+pasting or dragging+dropping groups of cells around the spreadsheet. I've explained this to the others (numerous times) and asked for them not to do this and if removing stuff, to simply highlight the cells and click delete, but it appears they don't fully understand. Is there any way to prevent this from happening, as it's a real pain sort it out every time! I've seen other forums where people mention protecting the worksheet and all that, but I want the others to be able to move stuff around, just not causing the formulas and stuff to be removed.