[sumesh56]

i have a spreadsheet of seating plan of students. i have to get a particular background color(fill color) to numbers which starts from 1 to 7. to make it more clear,
if the numbers start from 1 ie,101 to 199 the cells which contain should have blue color.

if the numbers start from 2 ie,201 to 299 the cells which contain should have orange color.

if the numbers start from 3 ie,301 to 399 the cells which contain should have green color.

if the numbers start from 4 ie,101 to 499 the cells which contain should have red color.

if the numbers start from 5 ie,501 to 599 the cells which contain should have lightblue color.

if the numbers start from 6 ie,601 to 199 the cells which contain should have purple color.

if the numbers start from 7 ie,701 to 799 the cells which contain should have light green color.

kindly see the attachment. table one and three shows the same numbers.in table three i have given the fill color option. this should be the outcome for table two also.



after entering the number , we can easily make the background color using the fill color option. that is not the issue. the issue is when i enter a number in a particular cell it should show with the background color of my choice.

suggest a method to do this automatically.the cells are to be so formatted that when i enter a number it should show the fill color with the color which is predefined according to the first digit.please note that there is no permanent place for a particular number in all the tables.it will vary.it can easily be understood from the attachment table examples( as i pointed out above, if it is 110 it should be in blue.

[Claus Busch]

Hi,

Am Fri, 27 Sep 2013 16:47:40 +0100 schrieb sumesh56:

if the numbers start from 7 ie,701 to 799 the cells which contain should
have light green color.

in your table the numbers starting with 7 have red backcolor.

select D11:N15 = Conditional Formatting = use formula ...=
Formula: =--LEFT(D11,1)=7 and choose red background color.
Repeat it for your other values.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[sumesh56]

Originally Posted by sumesh56

i have a spreadsheet of seating plan of students. i have to get a particular background color(fill color) to numbers which starts from 1 to 7. to make it more clear,
if the numbers start from 1 ie,101 to 199 the cells which contain should have blue color.

if the numbers start from 2 ie,201 to 299 the cells which contain should have orange color.

if the numbers start from 3 ie,301 to 399 the cells which contain should have green color.

if the numbers start from 4 ie,101 to 499 the cells which contain should have red color.

if the numbers start from 5 ie,501 to 599 the cells which contain should have lightblue color.

if the numbers start from 6 ie,601 to 199 the cells which contain should have purple color.

if the numbers start from 7 ie,701 to 799 the cells which contain should have light green color.

kindly see the attachment. table one and three shows the same numbers.in table three i have given the fill color option. this should be the outcome for table two also.



after entering the number , we can easily make the background color using the fill color option. that is not the issue. the issue is when i enter a number in a particular cell it should show with the background color of my choice.

suggest a method to do this automatically.the cells are to be so formatted that when i enter a number it should show the fill color with the color which is predefined according to the first digit.please note that there is no permanent place for a particular number in all the tables.it will vary.it can easily be understood from the attachment table examples( as i pointed out above, if it is 110 it should be in blue.

i got an idea to solve the problem. select all the cells . then go to conditional formatting. select between. put the ranges and select the color of your choice .done!. hands off to excel 2007.