Excel Conditional Formatting : Change Cell Colour Based on Value of Another
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
For each cell value I would like to change the colour in an adjacent cell based on the value in another. For example:
I have a column of cells with a range on numbers from 81 to -91.
Where cell values are between -30 and -91 the colour is RED
Where cell values are between 0 and -30 the colour is AMBER
Where cell values are between 1 and 81 the colour is GREEN
worksheet-function
asked Sep 30 '18 at 17:52
GRAHAMGRAHAM
62
add a comment |
For each cell value I would like to change the colour in an adjacent cell based on the value in another. For example:
I have a column of cells with a range on numbers from 81 to -91.
Where cell values are between -30 and -91 the colour is RED
Where cell values are between 0 and -30 the colour is AMBER
Where cell values are between 1 and 81 the colour is GREEN
worksheet-function
asked Sep 30 '18 at 17:52
GRAHAMGRAHAM
62
1
You might try search the internet for "Excel between".
– BillDOe
Sep 30 '18 at 19:32
the challenge is to put "to change the colour in an adjacent cell based on the value in another" relative equation in the rule..
– p._phidot_
Oct 15 '18 at 21:30
add a comment |
For each cell value I would like to change the colour in an adjacent cell based on the value in another. For example:
I have a column of cells with a range on numbers from 81 to -91.
Where cell values are between -30 and -91 the colour is RED
Where cell values are between 0 and -30 the colour is AMBER
Where cell values are between 1 and 81 the colour is GREEN
worksheet-function
asked Sep 30 '18 at 17:52
GRAHAMGRAHAM
62
For each cell value I would like to change the colour in an adjacent cell based on the value in another. For example:
I have a column of cells with a range on numbers from 81 to -91.
Where cell values are between -30 and -91 the colour is RED
Where cell values are between 0 and -30 the colour is AMBER
Where cell values are between 1 and 81 the colour is GREEN
worksheet-function
worksheet-function
asked Sep 30 '18 at 17:52
GRAHAMGRAHAM
62
asked Sep 30 '18 at 17:52
GRAHAMGRAHAM
62
asked Sep 30 '18 at 17:52
GRAHAMGRAHAM
62
asked Sep 30 '18 at 17:52
GRAHAMGRAHAM
62
asked Sep 30 '18 at 17:52
GRAHAMGRAHAM
62
62
1
You might try search the internet for "Excel between".
– BillDOe
Sep 30 '18 at 19:32
the challenge is to put "to change the colour in an adjacent cell based on the value in another" relative equation in the rule..
– p._phidot_
Oct 15 '18 at 21:30
add a comment |
1
You might try search the internet for "Excel between".
– BillDOe
Sep 30 '18 at 19:32
the challenge is to put "to change the colour in an adjacent cell based on the value in another" relative equation in the rule..
– p._phidot_
Oct 15 '18 at 21:30
1
1
You might try search the internet for "Excel between".
– BillDOe
Sep 30 '18 at 19:32
You might try search the internet for "Excel between".
– BillDOe
Sep 30 '18 at 19:32
the challenge is to put "to change the colour in an adjacent cell based on the value in another" relative equation in the rule..
– p._phidot_
Oct 15 '18 at 21:30
the challenge is to put "to change the colour in an adjacent cell based on the value in another" relative equation in the rule..
– p._phidot_
Oct 15 '18 at 21:30
add a comment |
1 Answer
1
active
oldest
votes
I think this is what you wanted. Basically, in Excel you can reference cells in different ways. You may sometimes realise that when you click on a cell to reference it in your formula, it's inserted as $A$2 instead of A2. While both are correct, if for instance you start to drag around the former, the formula will be copied directly (e.g if you drag it to A3 it will not change, but remain as $A$2="Hi" in both cells). If you do the same to the latter, the formula will adapt (e.g if you drag it to A3 it will change from A2="Hi" to A3="Hi"). Also note that it is possible to only use one dollar sign/locked reference, such as $A3 (where it will change when dragged on the Y axis, but not the X) or A$3 (where it will change when dragged on the X axis, but not the Y).
Keeping this in mind, we can begin to create our formula. Select the cells you want to be coloured. Click 'Conditional Formatting' > 'New Rule' > 'Use a formula to determine which cells to format'. Click on the formula box. If you just click on a cell from there to select it, it will insert it as $A$2. However, this means that each cell's colour is being determined by the cell A2, as it is locked to only look at that cell. However, if you remove the second dollar sign to get $A2, then it will still only make comparisons in the A column, while looking at the relative row, as the row is open to change. Excel will automatically line up the row with the row it is testing to format, and therefore check in the right place.
In conclusion, make three formulae, all on the same range (the one you want to be coloured). The first, =$A2>0, and set the format to have a green fill. The second, =AND($A2>-31, $A2<1), and have an amber fill. Finally, do =$A2<-30 with a red fill.
I hope this helps.
answered Feb 7 at 21:27
Geza KerecsenyiGeza Kerecsenyi
1799
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1362700%2fexcel-conditional-formatting-change-cell-colour-based-on-value-of-another%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think this is what you wanted. Basically, in Excel you can reference cells in different ways. You may sometimes realise that when you click on a cell to reference it in your formula, it's inserted as $A$2 instead of A2. While both are correct, if for instance you start to drag around the former, the formula will be copied directly (e.g if you drag it to A3 it will not change, but remain as $A$2="Hi" in both cells). If you do the same to the latter, the formula will adapt (e.g if you drag it to A3 it will change from A2="Hi" to A3="Hi"). Also note that it is possible to only use one dollar sign/locked reference, such as $A3 (where it will change when dragged on the Y axis, but not the X) or A$3 (where it will change when dragged on the X axis, but not the Y).
Keeping this in mind, we can begin to create our formula. Select the cells you want to be coloured. Click 'Conditional Formatting' > 'New Rule' > 'Use a formula to determine which cells to format'. Click on the formula box. If you just click on a cell from there to select it, it will insert it as $A$2. However, this means that each cell's colour is being determined by the cell A2, as it is locked to only look at that cell. However, if you remove the second dollar sign to get $A2, then it will still only make comparisons in the A column, while looking at the relative row, as the row is open to change. Excel will automatically line up the row with the row it is testing to format, and therefore check in the right place.
In conclusion, make three formulae, all on the same range (the one you want to be coloured). The first, =$A2>0, and set the format to have a green fill. The second, =AND($A2>-31, $A2<1), and have an amber fill. Finally, do =$A2<-30 with a red fill.
I hope this helps.
answered Feb 7 at 21:27
Geza KerecsenyiGeza Kerecsenyi
1799
add a comment |
I think this is what you wanted. Basically, in Excel you can reference cells in different ways. You may sometimes realise that when you click on a cell to reference it in your formula, it's inserted as $A$2 instead of A2. While both are correct, if for instance you start to drag around the former, the formula will be copied directly (e.g if you drag it to A3 it will not change, but remain as $A$2="Hi" in both cells). If you do the same to the latter, the formula will adapt (e.g if you drag it to A3 it will change from A2="Hi" to A3="Hi"). Also note that it is possible to only use one dollar sign/locked reference, such as $A3 (where it will change when dragged on the Y axis, but not the X) or A$3 (where it will change when dragged on the X axis, but not the Y).
Keeping this in mind, we can begin to create our formula. Select the cells you want to be coloured. Click 'Conditional Formatting' > 'New Rule' > 'Use a formula to determine which cells to format'. Click on the formula box. If you just click on a cell from there to select it, it will insert it as $A$2. However, this means that each cell's colour is being determined by the cell A2, as it is locked to only look at that cell. However, if you remove the second dollar sign to get $A2, then it will still only make comparisons in the A column, while looking at the relative row, as the row is open to change. Excel will automatically line up the row with the row it is testing to format, and therefore check in the right place.
In conclusion, make three formulae, all on the same range (the one you want to be coloured). The first, =$A2>0, and set the format to have a green fill. The second, =AND($A2>-31, $A2<1), and have an amber fill. Finally, do =$A2<-30 with a red fill.
I hope this helps.
answered Feb 7 at 21:27
Geza KerecsenyiGeza Kerecsenyi
1799
add a comment |
I think this is what you wanted. Basically, in Excel you can reference cells in different ways. You may sometimes realise that when you click on a cell to reference it in your formula, it's inserted as $A$2 instead of A2. While both are correct, if for instance you start to drag around the former, the formula will be copied directly (e.g if you drag it to A3 it will not change, but remain as $A$2="Hi" in both cells). If you do the same to the latter, the formula will adapt (e.g if you drag it to A3 it will change from A2="Hi" to A3="Hi"). Also note that it is possible to only use one dollar sign/locked reference, such as $A3 (where it will change when dragged on the Y axis, but not the X) or A$3 (where it will change when dragged on the X axis, but not the Y).
Keeping this in mind, we can begin to create our formula. Select the cells you want to be coloured. Click 'Conditional Formatting' > 'New Rule' > 'Use a formula to determine which cells to format'. Click on the formula box. If you just click on a cell from there to select it, it will insert it as $A$2. However, this means that each cell's colour is being determined by the cell A2, as it is locked to only look at that cell. However, if you remove the second dollar sign to get $A2, then it will still only make comparisons in the A column, while looking at the relative row, as the row is open to change. Excel will automatically line up the row with the row it is testing to format, and therefore check in the right place.
In conclusion, make three formulae, all on the same range (the one you want to be coloured). The first, =$A2>0, and set the format to have a green fill. The second, =AND($A2>-31, $A2<1), and have an amber fill. Finally, do =$A2<-30 with a red fill.
I hope this helps.
answered Feb 7 at 21:27
Geza KerecsenyiGeza Kerecsenyi
1799
I think this is what you wanted. Basically, in Excel you can reference cells in different ways. You may sometimes realise that when you click on a cell to reference it in your formula, it's inserted as $A$2 instead of A2. While both are correct, if for instance you start to drag around the former, the formula will be copied directly (e.g if you drag it to A3 it will not change, but remain as $A$2="Hi" in both cells). If you do the same to the latter, the formula will adapt (e.g if you drag it to A3 it will change from A2="Hi" to A3="Hi"). Also note that it is possible to only use one dollar sign/locked reference, such as $A3 (where it will change when dragged on the Y axis, but not the X) or A$3 (where it will change when dragged on the X axis, but not the Y).
Keeping this in mind, we can begin to create our formula. Select the cells you want to be coloured. Click 'Conditional Formatting' > 'New Rule' > 'Use a formula to determine which cells to format'. Click on the formula box. If you just click on a cell from there to select it, it will insert it as $A$2. However, this means that each cell's colour is being determined by the cell A2, as it is locked to only look at that cell. However, if you remove the second dollar sign to get $A2, then it will still only make comparisons in the A column, while looking at the relative row, as the row is open to change. Excel will automatically line up the row with the row it is testing to format, and therefore check in the right place.
In conclusion, make three formulae, all on the same range (the one you want to be coloured). The first, =$A2>0, and set the format to have a green fill. The second, =AND($A2>-31, $A2<1), and have an amber fill. Finally, do =$A2<-30 with a red fill.
I hope this helps.
answered Feb 7 at 21:27
Geza KerecsenyiGeza Kerecsenyi
1799
answered Feb 7 at 21:27
Geza KerecsenyiGeza Kerecsenyi
1799
answered Feb 7 at 21:27
Geza KerecsenyiGeza Kerecsenyi
1799
answered Feb 7 at 21:27
Geza KerecsenyiGeza Kerecsenyi
1799
1799
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1362700%2fexcel-conditional-formatting-change-cell-colour-based-on-value-of-another%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
You might try search the internet for "Excel between".
– BillDOe
Sep 30 '18 at 19:32
the challenge is to put "to change the colour in an adjacent cell based on the value in another" relative equation in the rule..
– p._phidot_
Oct 15 '18 at 21:30