Formating a specific color for a value?

[allisojh]

As a teacher I use Excell for grading. I would like to have the computer automaticly change the color of grades. For example an "A" with the values being 91-100 would be black, "B" with walues being 84-90 wuold be green, etc...

I realy want to use it with failures "F" being 0-69.

I change the color manually but sometimes I forget and it looks very unprofessional.

Can anyone help me find a easy solution?

[inarbeth]

On 2002-05-20 09:57, allisojh wrote:
As a teacher I use Excell for grading. I would like to have the computer automaticly change the color of grades. For example an "A" with the values being 91-100 would be black, "B" with walues being 84-90 wuold be green, etc...

I realy want to use it with failures "F" being 0-69.

I change the color manually but sometimes I forget and it looks very unprofessional.

Can anyone help me find a easy solution?

You can use Conditional Formatting.Click Format, Conditional Formatting and then apply your conditions. You can only do 3 colours plus the default with Conditional Formatting.

[Jack in the UK]

same question differ way put

suggest see my answer on http://www.mrexcel.com/board/viewtop...8856&forum=2&1

let me know if you need help//

VBA Script is below for ease, you can add in as many as you like conditions and the look up refernce and so on, let me know if you need more hlep.

Sub Jacks_Rows_Colour() ' Written by Jack in the UK 21 May 2002
On Error GoTo JACKintheUK
' Jack set error handle
Sheets("B W").Select
' Jack selets the required sheet
' Jack change as needed
Range("A1:R50000").Select
'Jack select the required range
Selection.Interior.ColorIndex = xlNone
' Jack removes any colours in the above range
Range("A1").Select
' Jack hey i choose a cell
Application.ScreenUpdating = False
'Jack removes screen updates FASTER code this way
jack = 1
' Jack sets Jack = 1
'>>>>>>>>>>>>>>>>>>>>
' Jack says Red you would like for a 0????
With Sheets("B W") 'Jack in the UK
For JackInThe_UK = 1 To WorksheetFunction.CountIf(.Columns("B:B"), "JackInThe_UK")
Set RJackInThe_UKFoundCell = .Columns("B:B").Find(What:="JackInThe_UK", _
After:=.Cells(jack, 2))
RJackInThe_UKFoundCell.EntireRow.Interior.ColorIndex = 4
'1= black 2= white 3= red 4=lime green 5= blue 6= yellow
jack = RJackInThe_UKFoundCell.Row
Next JackInThe_UK
End With

Columns("C:IV").Select
Selection.Interior.ColorIndex = xlNne
' Jack rips the colour from the right (removes it)
' set as required
' (Dont really need (left in case) dirty who cares?

Application.Goto Reference:="R1C1"
'Jack returns to a cell in this case A1
'(can be any i guess)
Application.ScreenUpdating = True
' Jack sets updates to live
JACKintheUK:
' Jack complete error handle
End Sub


Marker conditional format VBA

[inarbeth]

Dear Jack
Sorry but I can't get your code to run.

Is there an easy way to expand the Conditional Formatting option and allow, say, six different formats with the user being able to select the criteria for the conditions and also the type of formatting? I expect the code is long but I wonder whether one of the Mr Excel gurus has worked on this and could share his or her knowledge.

[Nimrod]

Hi Alli:
I justed dashed this code off so you will need to make a few changes
1) .column =1 , needs to be changed to what ever column your grades are in
2) you will need to change the numbers in the case statement to meet your axact grade ranges
3) you will need to change the color value (i.e. the # "4") to what ever colors you want.

Other than that just open you VBE and drop this in "ThisWorkBook" object in the left project window ....

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

With Target
    If .Column = 1 Then
    Select Case .Value
        Case 90 To 100 ' A
            .Interior.ColorIndex = 4
        Case 80 To 89 ' B
            .Interior.ColorIndex = 4
        Case 70 To 79 ' c
            .Interior.ColorIndex = 4
        Case 60 To 69 ' D
            .Interior.ColorIndex = 4
        Case Else
            .Interior.ColorIndex = 4
    End Select
    End If
End With
End Sub

Please let me know if this is suitable

NOTE: if you want it to be the Font instead of the cell that changes color then change ".Interior.ColorIndex = 4 "

to ".Font.ColorIndex = 4 "
[ This Message was edited by: Nimrod on 2002-05-21 00:47 ]

[ This Message was edited by: Nimrod on 2002-05-21 00:53 ]

[sen_edp]

check this out , seems that does the job

http://www.mrexcel.com/board/viewtopic.php?
topic=6974&forum=2&start=0

[Nimrod]

Here's a second version (see previous listing) that sets cell back to defaults if the grade is removed.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

With Target
    If .Column = 1 Then
    Select Case Val(.Value)
        Case 90 To 100 ' A
            .Interior.ColorIndex = 4
        Case 80 To 89 ' B
            .Interior.ColorIndex = 4
        Case 70 To 79 ' c
            .Interior.ColorIndex = 4
        Case 60 To 69 ' D
            .Interior.ColorIndex = 4
        Case 1 To 59  ' F
        .Interior.ColorIndex = 4
        Case Else 'reset to default if non of above
            .Interior.ColorIndex = xlNone
            .Font.ColorIndex = 0
    End Select
    End If
End With
End Sub

[ This Message was edited by: Nimrod on 2002-05-21 01:15 ]

[sen_edp]

hello

check this ,too

http://www.mrexcel.com/board/viewtop...orum=2&start=0

[kaptep]

I am looking for something similar to this, but I am not sure how to get this code to run and actually do the formatting. I have pasted the code into the workbook under VBA but I am not getting anything to change. Can you give me some more guidance here.