Aladin..plz check your mail..coz i've sent you the file about the conditional formating problem :-)

Done... It's underway to you. (NT)

( NM ) CHECK your mail again Aladin.. a little problem occured...Re: Done... It's underway to you. (NT)

This code works!

This macro will first test your range for all blanks, if all cells in A1:F1 are blank then it will Gray the range.
If any cell in the range is less than but not equal to 40 and not equal to 0, the range will change to Red.
Last if any cell in the range is equal to or greater than 40, the range will change to Blue.

This is the best I can do, the problem is: If one cell in the range has the value 2(A Red value in theory) and another 50(Blue in theory) then the whole range will be Blue, due to the last test, which Blues the range if any cell in the range is greater or equal to 40! It works by hierarchy or events. All empty then Gray, no cell over 40 then Red, any cell over 40 then Blue.

Sub ColorGet()
'
Dim vTest
Range("A1:F1").Select

'If value tests.
'Gray.
If Range("A1").Value = "" Or Range("B1").Value = "" Or _
Range("C1").Value = "" Or Range("D1").Value = "" Or _
Range("E1").Value = "" Or Range("F1").Value = "" Then
vTest = 3
End If

'Red.
If ((Range("A1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("B1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("C1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("D1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("E1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("F1").Value < 40 And Range("A1").Value <> 0)) Then
vTest = 1
End If

'Blue.
If Range("A1").Value >= 40 Or Range("B1").Value >= 40 Or _
Range("C1").Value >= 40 Or Range("D1").Value >= 40 Or _
Range("E1").Value >= 40 Or Range("F1").Value >= 40 Then
vTest = 2
End If

'Colors.
'Red.
If vTest = 1 Then
Selection.FormatConditions.Delete
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else

'Blue.
If vTest = 2 Then
Selection.FormatConditions.Delete
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else

'Gray.
If vTest = 3 Then
With Selection.Interior
Selection.FormatConditions.Delete
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End If
End If

Range("A1").Select
End Sub

Hope this helps you. If the hierarchy of color events are not to your liking, re-order them!
I tested this with a Hot-key, Ctrl-c. It works fine and updates for new values with each Ctrl-c press. JSW

Igor's Latest Specs

In order to keep any party interested informed, here are Igor's specs, received off-line:

The cells of interest are A1, C1, and E1.
The range of interest is A1:F1.

If A1, C1, and E1 each empty (meaning no numbers/marks entered), all cells of the range should turn grey (cell background grey).
If the count of marks is less than 3, all cells of the range should turn red.
If the count of marks is 3 and any of these marks is less than 40, all cells of the range should turn red.
If the count of marks is 3 and each of these marks is equal to or greater than 40, all cells of the range should turn blue.

BTW, blue = PASS, red = FAIL.

All conditions above can be covered in terms of 3 conditions that Conditional Formatting allows. That's what I did (You might want to simplify the formulas.)

Condition 1

=OR(AND(SUM($A1,$C1,$E1)>0,COUNT($A1,$C1,$E1) < 3),AND($A1<>0,$A1 < 40),AND($C1<>0,$C1 < 40),AND($E1<>0,$E1 < 40))

Condition 2

=AND($A1<>0,$A1>=40,$C1<>0,$C1>=40,$E1<>0,$E1>=40)

Condition 3

=AND(LEN($A1)=0,LEN($C1)=0,LEN($E1)=0)

Aladin

Re: This code works! Thanks John for hard work..cheers

Range("A1").Select