SOLVED

Assign color to specific numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-180382%22%20slang%3D%22en-US%22%3EAssign%20color%20to%20specific%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-180382%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%60m%20a%20new%20member%20in%20this%20forum%20and%20this%20is%20my%20first%20post%20here.%3C%2FP%3E%3CP%3EI%20use%20excel%20more%20and%20more%20in%20my%20day%20job%20and%20have%20discovered%20that%20excel%20is%20a%20very%20powerful%20tool%20with%20tons%20of%20functionality%20that%20can%20be%20used%20in%20various%20ways.%3C%2FP%3E%3CP%3EI%20try%20to%20learn%20as%20I%20go%20along%20within%20excel.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20to%20my%20question%20this%20time%3A%3C%2FP%3E%3CP%3EHow%20can%20I%20assign%20colors%20that%20I%20choice%20to%20specific%20numbers%20in%20excel%3F%26nbsp%3B%3C%2FP%3E%3CP%3E1%20equals%20green%2C%202%20equals%20blue%20etc...%20so%20every%20time%20a%20user%20press%201%20in%20any%20cell%20within%20the%20specific%20sheet%20the%20background%20color%20will%20change%20according%20the%20color%20assigned%20to%20the%20specific%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopes%20this%20makes%20sense%2C%20and%20that%20someone%20are%20able%20to%20help.%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20a%20very%20simple%20example%20file%20to%20help%20explain%20what%20I%20want%20to%20achieve.%20This%20is%20basically%20for%20my%20learning%20howto%20do%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBR%26nbsp%3B%3C%2FP%3E%3CP%3ENiclas%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-180382%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAssign%20specific%20color%20to%20custom%20numbers%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-180395%22%20slang%3D%22en-US%22%3ERe%3A%20Assign%20color%20to%20specific%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-180395%22%20slang%3D%22en-US%22%3EVery%20good.%3CBR%20%2F%3EThanks%20alot.%3CBR%20%2F%3EWorks%20perfectly.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-180393%22%20slang%3D%22en-US%22%3ERe%3A%20Assign%20color%20to%20specific%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-180393%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Niclas%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20do%20that%20with%20another%20technique%20along%20with%20the%20Conditional%20Formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20you%20finish%20the%20previous%20steps%20and%20apply%20the%20colors%20to%20the%20numbers%3A%3C%2FP%3E%3CUL%3E%3CLI%3ESelect%20the%20range%20of%20numbers.%3C%2FLI%3E%3CLI%3EClick%20%3CSTRONG%3ECtrl%2B1%20%3C%2FSTRONG%3Eto%20open%20the%3CSTRONG%3E%20Format%20Cells%3C%2FSTRONG%3E%20dialog%20box.%3C%2FLI%3E%3CLI%3EFrom%20the%20%3CSTRONG%3ENumber%3C%2FSTRONG%3E%20tab%2C%20select%3CSTRONG%3E%20Custom%20%3C%2FSTRONG%3Ecategory.%3C%2FLI%3E%3CLI%3EIn%20the%20%3CSTRONG%3EType%20%3C%2FSTRONG%3Ebox%2C%20type%20double%20quotation%20mark%20twice%3CSTRONG%3E%26nbsp%3B%22%22%3C%2FSTRONG%3E%20and%20then%20hit%3CSTRONG%3E%20OK%3C%2FSTRONG%3E.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-180387%22%20slang%3D%22en-US%22%3ERe%3A%20Assign%20color%20to%20specific%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-180387%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Haytham%2C%3C%2FP%3E%3CP%3EFirst%20of%20all%2C%20thanks%20alot%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20additional%20thing%20tho.%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20achieve%20that%20the%20numbers%20actually%20disappears%20in%20the%20actual%20cell%3F%20So%20only%20the%20color%20is%20visible%3F%26nbsp%3B%3C%2FP%3E%3CP%3EBr%3C%2FP%3E%3CP%3ENiclas%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-180385%22%20slang%3D%22en-US%22%3ERe%3A%20Assign%20color%20to%20specific%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-180385%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Niclas%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWelcome%20to%20Microsoft%20Excel%20Community!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20a%20powerful%20tool%20in%20Excel%20called%20%3CSTRONG%3E(Conditional%20Formatting)%3C%2FSTRONG%3E%2C%20t%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%3Ehrough%20this%20tool%20you%20can%20achieve%20what%20you%20asking%20for%20in%20the%20following%20steps%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CUL%3E%3CLI%3ES%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%3Eelect%20the%20range%20(for%20example%20A1%3AB5)%20that%20you%20want%20to%20assign%20the%20colors%20to%20it.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%3EGo%20to%20Home%20%26gt%3B%26gt%3B%20Styles%20%26gt%3B%26gt%3B%20Conditional%20Formatting%20%26gt%3B%26gt%3B%20Highlight%20Cells%20Rules%20%26gt%3B%26gt%3B%20Equal%20To.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%3EIn%26nbsp%3B%3CSTRONG%3EEqual%20To%3C%2FSTRONG%3E%20dialog%20box%20put%20the%20number%20and%20assign%20the%20color%20that%20you%20want%20to%20it%2C%20then%20click%20OK.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%3EDo%20the%20same%20steps%20for%20each%20number.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%3EI%20hope%20this%20helps%20you%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1184280%22%20slang%3D%22en-US%22%3ERe%3A%20Assign%20color%20to%20specific%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1184280%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EUnfortunately%20when%20i%20tried%20this%20more%20than%201%20time%20the%20function%20is%20no%20longer%20available.%3C%2FP%3E%3CP%3EI%20tried%20it%20and%20it%20work%20more%20or%20less%20perfect%2C%20but%20just%20for%20the%20first%20time.%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20something%20that%C2%B4s%20wrong%20with%20my%20excel%20or%20is%20it%20a%20setting%3F%3C%2FP%3E%3CP%3EThanks%20fro%20your%20time%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1713500%22%20slang%3D%22en-US%22%3ERe%3A%20Assign%20color%20to%20specific%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1713500%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20doesn't%20help%20It%20only%20gives%20pre%20determined%20colors%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello all, 

I`m a new member in this forum and this is my first post here.

I use excel more and more in my day job and have discovered that excel is a very powerful tool with tons of functionality that can be used in various ways.

I try to learn as I go along within excel. 

 

No to my question this time:

How can I assign colors that I choice to specific numbers in excel? 

1 equals green, 2 equals blue etc... so every time a user press 1 in any cell within the specific sheet the background color will change according the color assigned to the specific number.

 

Hopes this makes sense, and that someone are able to help. 

Attached a very simple example file to help explain what I want to achieve. This is basically for my learning howto do it.

 

BR 

Niclas

6 Replies
Highlighted

Hi Niclas,

 

Welcome to Microsoft Excel Community!

 

There is a powerful tool in Excel called (Conditional Formatting), through this tool you can achieve what you asking for in the following steps:

  • Select the range (for example A1:B5) that you want to assign the colors to it.
  • Go to Home >> Styles >> Conditional Formatting >> Highlight Cells Rules >> Equal To.
  • In Equal To dialog box put the number and assign the color that you want to it, then click OK.
  • Do the same steps for each number.

 

I hope this helps you

Highlighted

Hello Haytham,

First of all, thanks alot for your help.

 

An additional thing tho.

Is it possible to achieve that the numbers actually disappears in the actual cell? So only the color is visible? 

Br

Niclas

Highlighted
Best Response confirmed by Niclas Johansson (Occasional Contributor)
Solution

Hi Niclas,

 

You can do that with another technique along with the Conditional Formatting.

 

After you finish the previous steps and apply the colors to the numbers:

  • Select the range of numbers.
  • Click Ctrl+1 to open the Format Cells dialog box.
  • From the Number tab, select Custom category.
  • In the Type box, type double quotation mark twice "" and then hit OK.

 

Highlighted
Very good.
Thanks alot.
Works perfectly.
Highlighted

@Haytham Amairah 

Hi,

Unfortunately when i tried this more than 1 time the function is no longer available.

I tried it and it work more or less perfect, but just for the first time. 

Is it something that´s wrong with my excel or is it a setting?

Thanks fro your time

Highlighted

This doesn't help It only gives pre determined colors@Haytham Amairah