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

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

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

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

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 ?

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

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 ?

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.

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 ?