how to format cells -fill color

[sumesh56]

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

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

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

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

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

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

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

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



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

suggest a method to do this automatically.the cells are to be s
formatted that when i enter a number it should show the fill color wit
the color which is predefined according to the first digit.( as
pointed out above, if it is 110 it should be in blue

+-------------------------------------------------------------------
|Filename: test seating plan.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=916
+-------------------------------------------------------------------

 

[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.

 

[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.


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+