Hi everyone,
when looking at the contributions, we are having two streams: to use
the normal way via cond. formatting and or using VBA. If we recall what
the "customer" (= dinadvani) initially wanted
(" 1) Ask the user on which he wants to apply the conditional
formatting
2) Once user selects the range, then apply conditional formatting
on it. ")
then I have the impression that the normal way is not what she/he
wanted to have. For me this is the call for an automated solution. Or
is this a mistinterpretation, dinadvani? You have to clarify this. If
you are satisfied with the normal solution, fine. Implication for you:
write a manual for the user how to handle cond. formatting. The other
option is to e.g. implement a button or an additional menu to do the
whole thing (except the range selection) with one mouse click.
I have tested my code again, it still works. It is correct, that we
could reduce the conditions to three, but that is just details.
Regarding not colouring the blank cells, I will think about a way to
handle this and let you know the next days.
Regards
Udo
Dallman said:
[QUOTE="dinadvani via OfficeKB.com said:
as Excel only accepts three conditions for cond. formatting,
you have to use a VBA solution:
Code looks good to me at a glance, but really, he did only have
three conditions. He *called* them 5, but they are three:
3) Below are the 5 conditions
a) 25 to 35 % - Interior colour green and font colour white
b) 20% to 25 % - - Interior colour gold and font colour white
c) 35 to 40% - - Interior colour gold and font colour white
d) below 20 % - - Interior colour red and font colour white
e) above 40%- Interior colour red and font colour white
Cell value =OR(<20%,>40% red/white
Cell value =OR(<25%,>=35%) gold/white
Cell Value =<=40% green/white
I *think* I got that right ...
I used the below code for this, but still i am unable to but the
bold fonts for the selection and also the blank cells also get
coloured. I don't want the blank cells to be coloured but its not
working with this code. Please help
The party who wrote the macro code will hopefully come back and
help. But I still say you don't even need the macro for this.
With the above suggestion of mine, which I still think should
work fine, just add another test ot the conditions.
I just tested this, and it seems to work as you wish.
Use "Formula Is" in the conditional format area. There
are three conditionals, in this order:
=AND(ISNUMBER(A1),OR(A1<20%,A1>40%)) '(set to red/white)
=AND(ISNUMBER(A1),OR(A1<25%,A1>=35%)) '(set to gold/white)
=AND(ISNUMBER(A1),A1<=40%) '(set to green/white)
The part beginning with an apostrophe (a.k.a. "single-quote")
is for you, not meant to be part of the formula.
-dman-
==========================================================
[Left in for context, from the other poster]
Private Sub Worksheet_Change() '(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Application.InputBox("Select range", Type:=8)
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is > 0.4: Num = 3 'red
Case 0.2 To 0.25: Num = 44 'amber
Case 0.25 To 0.35: Num = 4 'green
Case 0.35 To 0.4: Num = 44 'amber
Case Is < 0.2: Num = 3 'red
Case Is < 0#: Num = 2 'white
End Select
'Apply the color
rng.Interior.ColorIndex = Num
rng.Font.ColorIndex = 2
Next rng
End Sub
Dinesh
[/QUOTE]