Limitation of Conditional Formatting

[wilchong via OfficeKB.com]

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

 

[Gord Dibben]

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

 

[Learning Excel]

If not, then how can an add in be UNstalled ?

 

[Learning Excel]

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

 

[Gord Dibben]

Don't understand the question in the context of my post.

Please explain.


Gord

 

[Gord Dibben]

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 Tools>Add-ins and unchecked CFPlus.


Gord

 

[Learning Excel]

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.

 

[Gord Dibben]

Check my reply to your second post under same subject.


Gord

 

[Learning Excel]

I guess our posts have crossed between them.
THANKS for your help. Got it!