Excel 2003 conditional formatting?

Question

Column A is a number and B is a percent. I need to make column A different colors based on the percent in B. I need 69% and lower, 70 to 80%, 81 to 89 %, 91 to 99 %, and 100 percent or above.

Answer 1

Hi,
Sorry to tell you but conditional formatting has only THREE options, but the good thing is you can use VBA and do all and more want you want to do. Hope this helps.

Answer 2

William is right. For conditional formatting in Excel 2003 you can't use over three different colors. So you'd have to use a macro in that instance.

By the way, I noticed that you skipped over 90%.

Here is a macro that should help you:


Application.ScreenUpdating = False

Dim Grade As Double
For Each x In [Sheet1!B:B]
If IsNumeric(x) = True Then
Grade = Round(x, 0)
If x.Text <> "" Then
Select Case Grade
'Yellow
Case 0 To 69: x.Interior.ColorIndex = 6
'Blue
Case 70 To 80: x.Interior.ColorIndex = 41
'Red
Case 81 To 90: x.Interior.ColorIndex = 3
'Green
Case 91 To 99: x.Interior.ColorIndex = 4
'Orange
Case Is >= 100: x.Interior.ColorIndex = 45
Case Else: x.Interior.ColorIndex = xlNone
End Select
End If
End If
Next x

Go to Tools > Macro > Record New Macro

Then pick out cells A1 to A5 and color them the colors you want for the different grade ranges. Then hit the stop button.

Now go back to Tools > Macro > Macros...

A window should pop up. Select the macro you just created and click Edit. This will bring up the code for the macro you just recorded. Write down the color index numbers for the colors you chose based on the code the macro recorded. Now delete out all of the lines of code except the lines tha have the word Sub in them. Paste my code in between the two lines that have Sub in them. Then replace my index numbers with the ones you just wrote down. Change Sheet1 to whatever the name of your work sheet is. Then click the save button and close the window with the coding. Click save on your workbook.

Now go back and click:

Tools > Macro > Macros...

But this time you'll click the play button for the macro. This will run the macro you just created and colorize all of those grade cells in Column B.

Important Note: It's always good to save your work or save your work under a different name before you run a macro. You can't go back and hit the undo button to undo the changes that a macro made.

Answer 3

You can have up to four different colors in Excel 2003, with the three conditions in Conditional Formatting, plus the default color if none of the conditions are met.

So if it's possible to combine two of your categories into one (for example 90-99% and 100%+), you can do this:
1) select all of the cells that you want to format this way
2) format them all the way 69% or less should be
3) then do Format-Conditional and set different formats for these 3 conditions:
#1: Cell Value is between .7 and .799
#2: Cell Value is between .8 and .899
#3: Cell Value is greater than or equal to .9

An easier and more accurate way to do the conditions in 3) would be like this, since once a condition is satisfied, Excel stops looking:
#1: Cell Value is greater than or equal to .9
#2: Cell Value is greater than or equal to .8
#3: Cell Value is greater than or equal to .7

If you need more info, add detail, or e-mail.
Cheers.
.

Answer 4

If your numbers are stable you can do it easily. Base your color on formulas, not cell value.

For instance

=if((a1/$b$1)>.9,true,false)

Will result in a qualifier being created that will tell the cell to change color if your responses in a1 is more than 90% of total sample of $b$1.

Here is the tricky part. You can't stack if statements in a conditional format. You can but it will give you erroneous results because you don't want to have to reference $b$1 and $c$1 etc. (I think about this and it might work if you want to set up a row that says this equals percentage. I'll mull this over some). Anyway, you have to base everything on your formula. Make sure that you have one conditional format that says

Cell value - equal to - ="".

This will default the cell to a blank color background if there is no value in the target cell.

http://office.microsoft.com/en-us/excel/HA011116611033.aspx?pid=CL100570551033

______________________
Edit 1

I may have misread your question. If you are trying to make the cell a different color on more than 3 qualifiers, then you won't be able to do it.