conditional formatting

[dinadvani via OfficeKB.com]

Hello,

I am facing problems with conditional formatting.

I need the below to be done, please help -

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.
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


Please help me to perform this.

Thanks for your help

D

 

[Udo]

Hi,

as Excel only accepts three conditions for cond. formatting, you have
to use a VBA solution:
Sub RangeFormatting()

Dim RangEl As Range
Dim Area As String
Dim Message As String
Dim LF As String

LF = Chr(10) 'line feed
Message = "Please enter the range to be coloured" + LF + _
"The form is e.g. B8:C25"

Message = InputBox(Message, "Range entry")
Range(Message).Select
Selection.FormatConditions.Delete
For Each RangEl In Selection
Debug.Print RangEl.Value
Select Case RangEl.Value
Case Is < 0.2
RangEl.Font.ColorIndex = 2
RangEl.Interior.ColorIndex = 9
Case Is < 0.25
RangEl.Font.ColorIndex = 2
RangEl.Interior.ColorIndex = 44
Case Is < 0.35
RangEl.Font.ColorIndex = 2
RangEl.Interior.ColorIndex = 50
Case Is < 0.4
RangEl.Font.ColorIndex = 2
RangEl.Interior.ColorIndex = 44
Case Is > 0.4
RangEl.Font.ColorIndex = 2
RangEl.Interior.ColorIndex = 9
End Select

Next

Cells(1, 5).Select

End Sub

Hope that helps.
If you need support re. how to activate this, just ask.

Good luck
Udo

 

[Dallman Ross]

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:

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 ...

-dman-

 

[dinadvani via OfficeKB.com]

Code looks good to me at a glance, but really, he did only have three
conditions. He *called* them 5, but they are three:


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 ...

-dman-

Hello,

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


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

 

[Dallman Ross]

Code looks good to me at a glance, but really, he did only have
three conditions. He *called* them 5, but they are three:


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 ...

[/QUOTE]

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]

 

[Udo]

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

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]

 

[Udo]

Now, as promised, a suggestion how to make sure that the empty cells
are not coloured:
Enter the following code just before the end of the macro:

Range.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = xlAutomatic

Here, "Range" is the name of the range, which had been marked
previously.

Hope, this helps.

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 Ross wrote:
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