Conditional formatting slow on big selectionConditional formatting, entire row basedExcel - Conditional Formatting - insert rowFormatting a cell based on perpendicular row/column valuesConditional Formatting refering to the current rowExcel Conditional Formatting based on Other ColumnApplying Individual Conditional Formatting in VBA — VERY SLOWConditional formatting rules split and repeatedany way to coloring the identical neighbor cells in excel?How to merge conditional formating directives?Conditional formatting Excel results from cell, address functions
Quoting Keynes in a lecture
Why do Radio Buttons not fill the entire outer circle?
Why the "ls" command is showing the permissions of files in a FAT32 partition?
SOQL query causes internal Salesforce error
Identifying "long and narrow" polygons in with PostGIS
El Dorado Word Puzzle II: Videogame Edition
Sound waves in different octaves
Origin of pigs as a species
If Captain Marvel (MCU) were to have a child with a human male, would the child be human or Kree?
Does the Crossbow Expert feat's extra crossbow attack work with the reaction attack from a Hunter ranger's Giant Killer feature?
Possible Eco thriller, man invents a device to remove rain from glass
How were servants to the Kaiser of Imperial Germany treated and where may I find more information on them
How do I tell my boss that I'm quitting in 15 days (a colleague left this week)
Limit max CPU usage SQL SERVER with WSRM
Did I make a mistake by ccing email to boss to others?
Mimic lecturing on blackboard, facing audience
What does "tick" mean in this sentence?
How do you justify more code being written by following clean code practices?
Can I run 125kHz RF circuit on a breadboard?
Do I have to take mana from my deck or hand when tapping a dual land?
What the heck is gets(stdin) on site coderbyte?
How to I force windows to use a specific version of SQLCMD?
Alignment of six matrices
How do I prevent inappropriate ads from appearing in my game?
Conditional formatting slow on big selection
Conditional formatting, entire row basedExcel - Conditional Formatting - insert rowFormatting a cell based on perpendicular row/column valuesConditional Formatting refering to the current rowExcel Conditional Formatting based on Other ColumnApplying Individual Conditional Formatting in VBA — VERY SLOWConditional formatting rules split and repeatedany way to coloring the identical neighbor cells in excel?How to merge conditional formating directives?Conditional formatting Excel results from cell, address functions
I am using conditional formatting to create a poor man's gantt chart. The idea is to have the first column have the task, the second have the duration, and the third have the resource. Each of the 7 resources have their own color. Columns 4-100ish have conditional formatting applied to them such that when there is a character in a cell, upon exit, the cell takes on the color of that row's 3rd column. I accomplish this with the following formula...
=(INDIRECT(ADDRESS(ROW(),3))="Resource A")*(INDIRECT(ADDRESS(ROW(),COLUMN()))<>"")
I have 7 conditional formatting lines, one for each resource/color, all with Stop If True checked.
Each rule is applied to the entire grid, in this case =$H$6:$HI$93
My problem is this is really slow. If I enter a dash into the cell, it will take about 2 seconds for Excel to finish applying the conditional formatting.
Any suggestions on how to speed this formatting up other than the obvious...use less rules.
excel conditional-formatting
edited Mar 7 at 22:03
cybernetic.nomad
2,81121121
asked Mar 7 at 21:57
NewCreatureNewCreature
31
add a comment |
I am using conditional formatting to create a poor man's gantt chart. The idea is to have the first column have the task, the second have the duration, and the third have the resource. Each of the 7 resources have their own color. Columns 4-100ish have conditional formatting applied to them such that when there is a character in a cell, upon exit, the cell takes on the color of that row's 3rd column. I accomplish this with the following formula...
=(INDIRECT(ADDRESS(ROW(),3))="Resource A")*(INDIRECT(ADDRESS(ROW(),COLUMN()))<>"")
I have 7 conditional formatting lines, one for each resource/color, all with Stop If True checked.
Each rule is applied to the entire grid, in this case =$H$6:$HI$93
My problem is this is really slow. If I enter a dash into the cell, it will take about 2 seconds for Excel to finish applying the conditional formatting.
Any suggestions on how to speed this formatting up other than the obvious...use less rules.
excel conditional-formatting
edited Mar 7 at 22:03
cybernetic.nomad
2,81121121
asked Mar 7 at 21:57
NewCreatureNewCreature
31
That formula looks like it could boil down to=and($c6="Resource A", h6<>"")
– user11156232
Mar 7 at 22:24
add a comment |
I am using conditional formatting to create a poor man's gantt chart. The idea is to have the first column have the task, the second have the duration, and the third have the resource. Each of the 7 resources have their own color. Columns 4-100ish have conditional formatting applied to them such that when there is a character in a cell, upon exit, the cell takes on the color of that row's 3rd column. I accomplish this with the following formula...
=(INDIRECT(ADDRESS(ROW(),3))="Resource A")*(INDIRECT(ADDRESS(ROW(),COLUMN()))<>"")
I have 7 conditional formatting lines, one for each resource/color, all with Stop If True checked.
Each rule is applied to the entire grid, in this case =$H$6:$HI$93
My problem is this is really slow. If I enter a dash into the cell, it will take about 2 seconds for Excel to finish applying the conditional formatting.
Any suggestions on how to speed this formatting up other than the obvious...use less rules.
excel conditional-formatting
edited Mar 7 at 22:03
cybernetic.nomad
2,81121121
asked Mar 7 at 21:57
NewCreatureNewCreature
31
I am using conditional formatting to create a poor man's gantt chart. The idea is to have the first column have the task, the second have the duration, and the third have the resource. Each of the 7 resources have their own color. Columns 4-100ish have conditional formatting applied to them such that when there is a character in a cell, upon exit, the cell takes on the color of that row's 3rd column. I accomplish this with the following formula...
=(INDIRECT(ADDRESS(ROW(),3))="Resource A")*(INDIRECT(ADDRESS(ROW(),COLUMN()))<>"")
I have 7 conditional formatting lines, one for each resource/color, all with Stop If True checked.
Each rule is applied to the entire grid, in this case =$H$6:$HI$93
My problem is this is really slow. If I enter a dash into the cell, it will take about 2 seconds for Excel to finish applying the conditional formatting.
Any suggestions on how to speed this formatting up other than the obvious...use less rules.
excel conditional-formatting
excel conditional-formatting
edited Mar 7 at 22:03
cybernetic.nomad
2,81121121
asked Mar 7 at 21:57
NewCreatureNewCreature
31
edited Mar 7 at 22:03
cybernetic.nomad
2,81121121
asked Mar 7 at 21:57
NewCreatureNewCreature
31
edited Mar 7 at 22:03
cybernetic.nomad
2,81121121
edited Mar 7 at 22:03
cybernetic.nomad
2,81121121
edited Mar 7 at 22:03
cybernetic.nomad
2,81121121
2,81121121
asked Mar 7 at 21:57
NewCreatureNewCreature
31
asked Mar 7 at 21:57
NewCreatureNewCreature
31
asked Mar 7 at 21:57
NewCreatureNewCreature
31
31
That formula looks like it could boil down to=and($c6="Resource A", h6<>"")
– user11156232
Mar 7 at 22:24
add a comment |
That formula looks like it could boil down to=and($c6="Resource A", h6<>"")
– user11156232
Mar 7 at 22:24
That formula looks like it could boil down to
=and($c6="Resource A", h6<>"")– user11156232
Mar 7 at 22:24
That formula looks like it could boil down to
=and($c6="Resource A", h6<>"")– user11156232
Mar 7 at 22:24
add a comment |
1 Answer
1
active
oldest
votes
This should work as a rule, taking advantage of the way CF uses cell references in formulas:
=AND(H6<>"", $C6="Resource A")
Where H6 is the top-left cell in the range being formatted (make sure to select the range from the top-left). The $C6 will allow the row to change but the column will remain fixed.
answered Mar 7 at 22:08
Tim WilliamsTim Williams
88.6k97087
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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%2fstackoverflow.com%2fquestions%2f55053448%2fconditional-formatting-slow-on-big-selection%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
This should work as a rule, taking advantage of the way CF uses cell references in formulas:
=AND(H6<>"", $C6="Resource A")
Where H6 is the top-left cell in the range being formatted (make sure to select the range from the top-left). The $C6 will allow the row to change but the column will remain fixed.
answered Mar 7 at 22:08
Tim WilliamsTim Williams
88.6k97087
add a comment |
This should work as a rule, taking advantage of the way CF uses cell references in formulas:
=AND(H6<>"", $C6="Resource A")
Where H6 is the top-left cell in the range being formatted (make sure to select the range from the top-left). The $C6 will allow the row to change but the column will remain fixed.
answered Mar 7 at 22:08
Tim WilliamsTim Williams
88.6k97087
add a comment |
This should work as a rule, taking advantage of the way CF uses cell references in formulas:
=AND(H6<>"", $C6="Resource A")
Where H6 is the top-left cell in the range being formatted (make sure to select the range from the top-left). The $C6 will allow the row to change but the column will remain fixed.
answered Mar 7 at 22:08
Tim WilliamsTim Williams
88.6k97087
This should work as a rule, taking advantage of the way CF uses cell references in formulas:
=AND(H6<>"", $C6="Resource A")
Where H6 is the top-left cell in the range being formatted (make sure to select the range from the top-left). The $C6 will allow the row to change but the column will remain fixed.
answered Mar 7 at 22:08
Tim WilliamsTim Williams
88.6k97087
answered Mar 7 at 22:08
Tim WilliamsTim Williams
88.6k97087
answered Mar 7 at 22:08
Tim WilliamsTim Williams
88.6k97087
answered Mar 7 at 22:08
Tim WilliamsTim Williams
88.6k97087
88.6k97087
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f55053448%2fconditional-formatting-slow-on-big-selection%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
That formula looks like it could boil down to
=and($c6="Resource A", h6<>"")– user11156232
Mar 7 at 22:24