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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 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
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies