Excel question

[Jackass]

this is actually for some schedule analysis i'm doing.

 

If you have a large grid, how would i make it so if a number 1 shows up, it's in 1 color, a 2 is another, etc. and then the colors would change if i change the #'s?

[gbpfan1231]

this is actually for some schedule analysis i'm doing.

 

If you have a large grid, how would i make it so if a number 1 shows up, it's in 1 color, a 2 is another, etc. and then the colors would change if i change the #'s?

Highlight all cells and then choose Format - Conditional Formatting and keep adding conditions until you are done.

[Jackass]

thanks. that worked well. only thing is it limits you to 3 criteria - i guess that's just a limitation of excel.

[Big Country]

thanks. that worked well. only thing is it limits you to 3 criteria - i guess that's just a limitation of excel.

 

Yes, 3 is the Excel limit. I believe it is possible to write some basic VBA code to run through additional criteria and set this up as a macro.

[lennykravitz2004]

Excel 2007 and 2010 allow up to 64 conditions, which is obviously not going to do you any good unless you want to go out and spend the money for one of those versions. Since I'm going to guess you don't want to do that..

 

http://www.ozgrid.com/VBA/excel-conditiona...tting-limit.htm

 

Excel has a very useful feature in Excel named Conditional Formatting . It can be found via Format>Conditional Formatting on the Worksheet Menu Bar. The feature allows us to format a cell based on its content. For example, if the cell is greater than 5, but less then 10, we may have the cell change to a red background. We can do this for up to 3 conditions only, which sometimes is not enough. If we want more than 3 conditions we can use Excel VBA code that is automatically fired whenever a user makes any change in a specified range. Let's assume we want to have 6 separate conditions in the range A1:A10 on a particular Worksheet. To do so try this. Activate the Worksheet then right click on its name tab and select "View Code". In here put the code as shown below. See here for details on Select Case Statement which can accommodate text & numbers.

 

Private Sub Worksheet_Change(ByVal Target As Range)

 

Dim icolor As Integer

 

 

 

If Not Intersect(Target, Range("A1:A10")) is Nothing Then

 

Select Case Target

 

Case 1 To 5

 

icolor = 6

 

Case 6 To 10

 

icolor = 12

 

Case 11 To 15

 

icolor = 7

 

Case 16 To 20

 

icolor = 53

 

Case 21 To 25

 

icolor = 15

 

Case 26 To 30

 

icolor = 42

 

Case Else

 

'Whatever

 

End Select

 

 

 

Target.Interior.ColorIndex = icolor

 

End If

 

 

 

End Sub

Now click the top right X, or use Alt+Q, to get back to your Worksheet. Enter some numbers in range A1:A10 between 1 to 3 and the background color of each cell will change. The color used is determined by the passing of a number to the variable icolor, which in turn passes this number to Target.Interior.ColorIndex The number that is passed is determined by the Case x To x For example, if we enter the number 22 in any cell within the range A1:A10 the number 15 is passed to icolor and then icolor (now having value of 15) is passed to the Target.Interior.ColorIndex making the cell grey. Target is always the cell that has changed and thus fired the code.

 

The only problem this leaves us with is what colors are represented by what numbers. We can obtain our needed number by recording a macro changing a cells background color, or you can go here for a Custom Function that will return a cells color by its ColorIndex number or by name