Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200611/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting
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 dinadvani via OfficeKB.com schrieb: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200611/1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting
In . com, Udo
spake thusly: 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 ... -dman- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting
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 ... -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 -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting
In <68ddfef0929d0@uwe, dinadvani via OfficeKB.com <u22798@uwe
spake thusly: 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%,A140%)) '(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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting
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 schrieb: In <68ddfef0929d0@uwe, dinadvani via OfficeKB.com <u22798@uwe spake thusly: 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%,A140%)) '(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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting
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. Udo schrieb: 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 schrieb: In <68ddfef0929d0@uwe, dinadvani via OfficeKB.com <u22798@uwe spake thusly: 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%,A140%)) '(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting a range with conditional formatting | Excel Worksheet Functions | |||
conditional formatting glitches | Excel Discussion (Misc queries) | |||
Keeping conditional formatting when sorting | Excel Discussion (Misc queries) | |||
conditional formatting | Excel Discussion (Misc queries) | |||
cannot use ISEVEN or ISODD functions in Conditional Formatting | Excel Worksheet Functions |