Home

Colour coding

%3CLINGO-SUB%20id%3D%22lingo-sub-806889%22%20slang%3D%22en-US%22%3EColour%20coding%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806889%22%20slang%3D%22en-US%22%3EI%20am%20trying%20to%20setup%20my%20spreadsheet%20so%20that%20one%20column%20will%20colour%20code%20based%20on%20the%20values%20in%20the%20previous%20column.%20I%20can%20work%20out%20how%20to%20create%20the%20conditional%20formatting%20to%20make%20it%20work%20for%20one%20row%20but%20I%20don%E2%80%99t%20know%20how%20to%20make%20it%20work%20for%20any%20row.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-806889%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-806948%22%20slang%3D%22en-US%22%3ERe%3A%20Colour%20coding%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806948%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392988%22%20target%3D%22_blank%22%3E%40oconnells%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20conditional%20formatting%20rule%20with%20the%20formula%20used%20relative%20reference%20on%20previous%20column.%20Let%20say%20your%20range%20is%20in%20columns%20B%3AG%20and%20you'd%20like%20to%20highlight%20values%20which%20are%20greater%20than%20ones%20in%20previous%20column.%20When%20rule%20formula%20will%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DB1%26gt%3BA1%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20the%20rule%20applied%20to%20the%20range%20%24B%3A%24G%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-806971%22%20slang%3D%22en-US%22%3ERe%3A%20Colour%20coding%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806971%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20reply.%20How%20do%20I%20set%20this%20up%3F%20I%20know%20where%20to%20put%20the%20formula.%20Where%20do%20I%20put%20the%20range%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-806987%22%20slang%3D%22en-US%22%3ERe%3A%20Colour%20coding%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806987%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392988%22%20target%3D%22_blank%22%3E%40oconnells%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20you%20range%20and%20apply%20CF%20rule%20using%20formula%20for%20the%20top%20left%20cell%20of%20the%20range.%20It%20will%20be%20applied%20to%20entire%20range%20automatically.%20However%2C%20you%20may%20always%20correct%20it.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20871px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127174i589CD26D02E1609E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-806998%22%20slang%3D%22en-US%22%3ERe%3A%20Colour%20coding%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806998%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20help.%20I%20have%20followed%20the%20instructions%20but%20it%E2%80%99s%20not%20working.%20When%20I%20click%20apply%20and%20come%20off%20the%20rules%20and%20then%20go%20back%20in%2C%20the%20dollar%20signs%20have%20been%20removed.%20Any%20idea%20why%3F%3CBR%20%2F%3E%3CBR%20%2F%3EAlso%20I%20do%20not%20have%20columns%20as%20letters%20-%20I%20have%20R4C8%20does%20this%20affect%20the%20formula%20that%20I%20need%20to%20put%20in%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-807018%22%20slang%3D%22en-US%22%3ERe%3A%20Colour%20coding%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807018%22%20slang%3D%22en-US%22%3ESergei%20-%20I%20have%20done%20it.%20You%20have%20no%20idea%20how%20grateful%20I%20am%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-807039%22%20slang%3D%22en-US%22%3ERe%3A%20Colour%20coding%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807039%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392988%22%20target%3D%22_blank%22%3E%40oconnells%3C%2FA%3E%26nbsp%3B%2C%20great%2C%20glad%20to%20know%20you%20sorted%20this%20out.%20Good%20luck%20with%20your%20project.%3C%2FP%3E%3C%2FLINGO-BODY%3E
oconnells
Occasional Contributor
I am trying to setup my spreadsheet so that one column will colour code based on the values in the previous column. I can work out how to create the conditional formatting to make it work for one row but I don’t know how to make it work for any row.
6 Replies

@oconnells 

You may use conditional formatting rule with the formula used relative reference on previous column. Let say your range is in columns B:G and you'd like to highlight values which are greater than ones in previous column. When rule formula will be

=B1>A1

and the rule applied to the range $B:$G

Thanks for the reply. How do I set this up? I know where to put the formula. Where do I put the range?

@oconnells 

Select you range and apply CF rule using formula for the top left cell of the range. It will be applied to entire range automatically. However, you may always correct it.

image.png

Please check in attached.

Thanks for your help. I have followed the instructions but it’s not working. When I click apply and come off the rules and then go back in, the dollar signs have been removed. Any idea why?

Also I do not have columns as letters - I have R4C8 does this affect the formula that I need to put in?
Sergei - I have done it. You have no idea how grateful I am

@oconnells , great, glad to know you sorted this out. Good luck with your project.

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies