SOLVED

How to change conditional formatting colour on different rows same formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3201607%22%20slang%3D%22en-US%22%3EHow%20to%20change%20conditional%20formatting%20colour%20on%20different%20rows%20same%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3201607%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20same%20formula%20for%20conditional%20formatting%20working%20well%20in%20a%20gantt%20chart%20I%20have%20created.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20I%20want%20different%20coloured%20bars%20using%20the%20same%20formula%20on%20different%20lines%20of%20my%20spreadsheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20change%20the%20colour%20formatting%20of%20the%20conditional%20formatting%20on%20one%20line%20in%20the%20spreadsheet%2C%20it%20changes%20all%20the%20colours%20in%20the%20sheet%20even%20though%20I%20only%20highlighted%20once%20instance%20or%20row%20of%20the%20conditional%20format%20to%20change.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3201607%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3202588%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20change%20conditional%20formatting%20colour%20on%20different%20rows%20same%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3202588%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1315130%22%20target%3D%22_blank%22%3E%40NSCexcel%3C%2FA%3E%26nbsp%3BThen%20you%20have%20to%20change%20the%20%22Applies%20to%22%20ranges%20and%20create%20separate%20rules%20for%20each%20range.%20So%2C%20you%20could%20set%20a%20condition%20to%20have%20one%20color%20applied%20to%2C%20for%20instance%20%24A2%3A%24H10%20and%20another%20rule%20%3CU%3Ewith%20the%20same%20condition%20but%20with%20a%26nbsp%3Bdifferent%20color%2C%3C%2FU%3E%20applied%20to%26nbsp%3B%24A11%3A%24H20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have the same formula for conditional formatting working well in a gantt chart I have created.

 

However I want different coloured bars using the same formula on different lines of my spreadsheet. 

 

If I change the colour formatting of the conditional formatting on one line in the spreadsheet, it changes all the colours in the sheet even though I only highlighted once instance or row of the conditional format to change.

 

 

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@NSCexcel Then you have to change the "Applies to" ranges and create separate rules for each range. So, you could set a condition to have one color applied to, for instance $A2:$H10 and another rule with the same condition but with a different color, applied to $A11:$H20.

 

Thank you @Riny_van_Eekelen 

 

You are absolutely right!

 

Best

Nic