Archive of Mr Excel Message Board

Activate A1, activate Format|Conditional Formatting, and choose Formula is.

Type in for Condition 1: =AND(ISNUMBER(A1),A1<=40), Format = Red
Type in for Condition 2: =AND(ISNUMBER(A1),A1>40), Format = Blue

Type in for Condition 1: =LEN(A1)=, Format = Grey

After OK, click Format Painter then select the range in question. You're done.

Aladin

==========

Menu: Format-Conditional Formatting...
Condition 1: "Formula Is"
=ISBLANK(Your Range)=TRUE
Format... Click Button
Patterns select cell shading "Gray"
OK OK

That will do it. JSW

Thanks guys but is not working..the problem
is i have set my range to a1:f1..so..

1. when there is marks under 39 and below in either 1 of the
range (a1,b1,c1,d1,e1,f1) , it will turn to red (any
one from the cell filled is enuff to turn it to red)

2. if there is 40 and above marks in range a1:f1
(all cell from a1:f1 must be filled with 40 or above
marks), it will turn to blue

3. if range a1:f1 is empty, it will turn to grey
(cell b2, d2 and f2 are set with vlookup formula)
(cell a1,c1,e1 is for user to keyin their marks)

i have succeeded with no.1 and no.2..my problem
is with no.3..when this is no marks in cell a1,c1
and e1..coz it turn to red..i need it to turn to grey..

hope you guys understand my question and can help
me..i try aladin and joe..but not working..many thanks

1. turn to red = OR(A1:F1<40) ==> i can do this

2. turn to blue = greater than 40 ==> i can do this

3. turn to grey = when there's nothing ==> this my problem

USE THE FORMULAS THAT I SUGGESTED EXACTLY (WITHOUT ANY DOLLARS IN THEM). YOU JUST SET UP ALL CONDITIONS FOR CELL A1 ONE BY ONE AS I DESCRIBED. THEN GO THE ICON BAR, HIT FORMAT PAINTER, THEN SELECT B1:F1 AND LET IT GO. EVERYTHING WILL BE OKAY EVEN FOR THE CELLS THAT HAVE A FORMULA.

Aladin

=============

Type in for Condition 1: =AND(ISNUMBER(A1),A1<=40), Format = Red

Thanks Aladin..the problem is i need to turn red,
blue or grey the whole range (a1:f1) simultaneously
when it meets the condition..any ideas..

Connie,

I'm curious: Can you send me your try-out? Perhaps I'm not describing the steps as fully/clearly as needed.

Aladin

What do you mean by simultaneously? What I suggested will color the whole range cell by cell according what is in a given cell on the basis of conditions that you specified. Am I missing something?

PS. I can send you a workbook showing the whole thing if you wish.

Aladin

Aladin,
I can't get beyond the error msg, so I have nothing completed to send you. But I'll describe my steps as literally as possible. I am starting with a blank sheet, activating A1, going to conditional formatting, choosing Formula is, and typing exactly the following in the box:
=AND(ISNUMBER(A1),A1<=40), Format = Red
If I stop at this point and hit "ok", pretending I only want this one condition, I immediately get a formula error msg box, and Excel is blackening the entire formula. I tried taking out the space between the last comma and the word "Format" and taking out the spaces between the = sign and the color name, and still got the same error msg. So, I couldn't get far enough to try painting the format into any of the other cells. I also tried putting the other two conditions in, and got the same errors. I'm puzzled. Hope this helps in the diagnosis of this one!
Connie :-) Connie, I'm curious: Can you send me your try-out? Perhaps I'm not describing the steps as fully/clearly as needed. Aladin

SURE IT DOES. MEA CULPA. With Format = Red I didn't mean a part of the formula to be entered; It was rather a shortcut to: "click Format on the conditional settings dialog, activate a tab then choose Red". It was a bad choice, especially because of the = sign.

Cheers.

Aladin

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

This is the best I can do, the problem is: If one cell in the range is 2(Red) and another 50(Blue) then the whole range will be Blue! 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