If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Limitation of Conditional Formatting
I use Conditional Formatting a lot! However, it only allow three conditional
formatting for each cell. In my case, I need 5 conditional formats some time, therefore, I have to choose three out of five. My question is that is there any way or with the help of excel formula, to achieve exactly the same result like conditional formatting. Thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
#2
|
|||
|
|||
Limitation of Conditional Formatting
If the data are numbers you can get up to 6 formats for Fonts.
See John McGimpsey's site. http://www.mcgimpsey.com/excel/conditional6.html Or download Bob Phillips' CFPlus add-in which will get you up to 30 formats. http://www.xldynamic.com/source/xld.....Download.html If neither of those options are suitable, you could use event code. Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("C4:IR30") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X") nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15) For Each rr In r icolor = 0 For I = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(I) Then icolor = nums(I) End If Next If icolor 0 Then rr.Interior.ColorIndex = icolor End If Next End Sub Gord Dibben MS Excel MVP On Fri, 23 May 2008 03:14:26 GMT, "wilchong via OfficeKB.com" u43231@uwe wrote: I use Conditional Formatting a lot! However, it only allow three conditional formatting for each cell. In my case, I need 5 conditional formats some time, therefore, I have to choose three out of five. My question is that is there any way or with the help of excel formula, to achieve exactly the same result like conditional formatting. Thanks, Wilchong |
#3
|
|||
|
|||
Limitation of Conditional Formatting
If not, then how can an add in be UNstalled ?
-- Socrates said: I only know, I don''''''''t know nothing. I say : I don''''''''t even know, I don''''''''t know nothing. "Gord Dibben" wrote: If the data are numbers you can get up to 6 formats for Fonts. See John McGimpsey's site. http://www.mcgimpsey.com/excel/conditional6.html Or download Bob Phillips' CFPlus add-in which will get you up to 30 formats. http://www.xldynamic.com/source/xld.....Download.html If neither of those options are suitable, you could use event code. Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("C4:IR30") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X") nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15) For Each rr In r icolor = 0 For I = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(I) Then icolor = nums(I) End If Next If icolor 0 Then rr.Interior.ColorIndex = icolor End If Next End Sub Gord Dibben MS Excel MVP On Fri, 23 May 2008 03:14:26 GMT, "wilchong via OfficeKB.com" u43231@uwe wrote: I use Conditional Formatting a lot! However, it only allow three conditional formatting for each cell. In my case, I need 5 conditional formats some time, therefore, I have to choose three out of five. My question is that is there any way or with the help of excel formula, to achieve exactly the same result like conditional formatting. Thanks, Wilchong |
#4
|
|||
|
|||
Limitation of Conditional Formatting
This add in is very helpfull, but after installing it , I can not copy and
paste from one worksheet to another worksheet. Is there a solution for that ? Thanks -- Socrates said: I only know, I don''''''''t know nothing. I say : I don''''''''t even know, I don''''''''t know nothing. "Gord Dibben" wrote: If the data are numbers you can get up to 6 formats for Fonts. See John McGimpsey's site. http://www.mcgimpsey.com/excel/conditional6.html Or download Bob Phillips' CFPlus add-in which will get you up to 30 formats. http://www.xldynamic.com/source/xld.....Download.html If neither of those options are suitable, you could use event code. Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("C4:IR30") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X") nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15) For Each rr In r icolor = 0 For I = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(I) Then icolor = nums(I) End If Next If icolor 0 Then rr.Interior.ColorIndex = icolor End If Next End Sub Gord Dibben MS Excel MVP On Fri, 23 May 2008 03:14:26 GMT, "wilchong via OfficeKB.com" u43231@uwe wrote: I use Conditional Formatting a lot! However, it only allow three conditional formatting for each cell. In my case, I need 5 conditional formats some time, therefore, I have to choose three out of five. My question is that is there any way or with the help of excel formula, to achieve exactly the same result like conditional formatting. Thanks, Wilchong |
#5
|
|||
|
|||
Limitation of Conditional Formatting
Don't understand the question in the context of my post.
Please explain. Gord On Sat, 24 May 2008 09:38:39 -0700, Learning Excel wrote: If not, then how can an add in be UNstalled ? |
#6
|
|||
|
|||
Limitation of Conditional Formatting
I would hope Bob can post a fix for that.
Quoted from his site................................ Whilst it is a simple matter to uninstall CFPlus, it is just an Excel add-in, CFPlus also installs code into the target workbook which remains even if CFPlus is uninstalled. This can become an annoyane as it checks whether CFPLus is installed, and warns you if not. A function will be added to CFPlus to remove this code from a nominated workbook, but in the meantime, you can download a small executable to remove the CFPlus code . .............................................. The link to the small executable seems to be broken. A workaround to get rid of the warning is to open Thisworkbook module and delete the code that the add-in wrote to that module. Do this after you have gone to ToolsAdd-ins and unchecked CFPlus. Gord On Sat, 24 May 2008 09:38:40 -0700, Learning Excel wrote: This add in is very helpfull, but after installing it , I can not copy and paste from one worksheet to another worksheet. Is there a solution for that ? Thanks |
#7
|
|||
|
|||
Limitation of Conditional Formatting
Hi Gord Dibben. This is not related to you but to the 2 link you provided.
I installed the ADD IN for conditional formatting and it can do up to 30 conditional formatting but if I go to do regular COPY / PASTE it does not allow me to, for some reazon the PASTE function is not available, however I can COPY PASTE in the same worksheet. This happened after installing the CF PLUS ADD IN. Like I said is not related to you, but How can I UNISTALL this ADD IN ( or any other ). Thanks. -- Socrates said: I only know, I don''''''''t know nothing. I say : I don''''''''t even know, I don''''''''t know nothing. "Gord Dibben" wrote: Don't understand the question in the context of my post. Please explain. Gord On Sat, 24 May 2008 09:38:39 -0700, Learning Excel wrote: If not, then how can an add in be UNstalled ? |
#8
|
|||
|
|||
Limitation of Conditional Formatting
Check my reply to your second post under same subject.
Gord On Sat, 24 May 2008 13:18:00 -0700, Learning Excel wrote: Hi Gord Dibben. This is not related to you but to the 2 link you provided. I installed the ADD IN for conditional formatting and it can do up to 30 conditional formatting but if I go to do regular COPY / PASTE it does not allow me to, for some reazon the PASTE function is not available, however I can COPY PASTE in the same worksheet. This happened after installing the CF PLUS ADD IN. Like I said is not related to you, but How can I UNISTALL this ADD IN ( or any other ). Thanks. |
#9
|
|||
|
|||
Limitation of Conditional Formatting
I guess our posts have crossed between them.
THANKS for your help. Got it! -- Socrates said: I only know, I don''''''''t know nothing. I say : I don''''''''t even know, I don''''''''t know nothing. "Learning Excel" wrote: Hi Gord Dibben. This is not related to you but to the 2 link you provided. I installed the ADD IN for conditional formatting and it can do up to 30 conditional formatting but if I go to do regular COPY / PASTE it does not allow me to, for some reazon the PASTE function is not available, however I can COPY PASTE in the same worksheet. This happened after installing the CF PLUS ADD IN. Like I said is not related to you, but How can I UNISTALL this ADD IN ( or any other ). Thanks. -- Socrates said: I only know, I don''''''''t know nothing. I say : I don''''''''t even know, I don''''''''t know nothing. "Gord Dibben" wrote: Don't understand the question in the context of my post. Please explain. Gord On Sat, 24 May 2008 09:38:39 -0700, Learning Excel wrote: If not, then how can an add in be UNstalled ? |
Thread Tools | |
Display Modes | |
|
|