Help! I am trying to change cell colours using conditional formatting but it's not working. I have an 'IF' formula in one of the cells I'm trying to change colours on so I'm assuming that's why. It's changing all the other cells except the one with the formula. Is there a way around it please?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Changing cell colours
Thanks Scott. Sorry this is probably going to get a bit complicated. I'm really new to this & teaching myself about excel formulas etc so I'm probably going about this all wrong.
Basically, I have two cells; the 1st I input a percentage. The 2nd cell then takes that and generates the range I've stipulated using this:
=IF(AND(G13<"",G13<31%),"<30%",IF(AND(G13>29%,G13<61%),"30%-60%",IF(AND(G13>59%,G13<76%),"60%-75%",IF(AND(G13>75%,G13<86%),"75%-85%",IF(G13>84%,">85%",IF(ISBLANK(G13),""))))))
Now what I'm wanting to do is change the colour of the 2 cells according to the percentage (not the range). So for example if it's 35% I want it to be a shade of red. If it's 96% I want a shade of blue.
So using the Condition Formatting tool, I've set this up & it changes the top cell that I've manually input, but even though I've selected both cells, it will not apply the colour to the below range where the formula is.
I hope that makes sense?
Basically, I have two cells; the 1st I input a percentage. The 2nd cell then takes that and generates the range I've stipulated using this:
=IF(AND(G13<"",G13<31%),"<30%",IF(AND(G13>29%,G13<61%),"30%-60%",IF(AND(G13>59%,G13<76%),"60%-75%",IF(AND(G13>75%,G13<86%),"75%-85%",IF(G13>84%,">85%",IF(ISBLANK(G13),""))))))
Now what I'm wanting to do is change the colour of the 2 cells according to the percentage (not the range). So for example if it's 35% I want it to be a shade of red. If it's 96% I want a shade of blue.
So using the Condition Formatting tool, I've set this up & it changes the top cell that I've manually input, but even though I've selected both cells, it will not apply the colour to the below range where the formula is.
I hope that makes sense?
Upvote
0
Oh just one other thing - I've just worked out how I can change the colour of both cells using the range but that's not good enough because within the range I need it to update 1 of 2 colours. So while 76% will be in the range of 75%-85%, I want it to be amber if 75%-79% but green if 80%-84%. this is the only anomaly otherwise the otherwise the others work fine.
Sorry to complicate things a bit more.
Sorry to complicate things a bit more.
Upvote
0
Scott T
Well-known Member
- Joined
- Dec 14, 2016
- Messages
- 2,636
- Office Version
- 365
- 2016
- Platform
- Windows
When you use a formula in CF on multiple cells CF will treat the formula like it was copied to the other cells. Just like if you copy a formula in a cell it will update the cell references as you copy unless it is locked.
Assuming your entered % is in A1 and the if formula is in A2 then you would select A1 and A2 and use. Note how the cell reference is absolute. This makes sure that the formula it is always looking at A1 instead of changed to A2 if you used a relative reference.
If you need more information on relative, absolute, and mixed references then see this link
http://www.excel-easy.com/functions/cell-references.html
Assuming your entered % is in A1 and the if formula is in A2 then you would select A1 and A2 and use. Note how the cell reference is absolute. This makes sure that the formula it is always looking at A1 instead of changed to A2 if you used a relative reference.
Code:
=$A$1=0.35If you need more information on relative, absolute, and mixed references then see this link
http://www.excel-easy.com/functions/cell-references.html
Upvote
0
