Something more than Conditional Formatting

[Gary]

Hi,

I have 7000 rows, 5 columns.

I need to highlight the entire row if the value in column 5 is X

conditional formatting is limited to 3. I may have more than 10 criterion.
i.e. X, Y, Z, A, B, C, D, E, F, G, etc etc


Please help.
Thanks

 

[Pete_UK]

You could download this free Add-in from Bob Phillips - it gives you up
to 30 conditional formats:

http://www.xldynamic.com/source/xld.CFPlus.Download.html

Hope this helps.

Pete

 

[Gary]

Thanks Pete,

I downloaded it but when i try to open the file. It says

Runtime error 9
Subscript Out Of Range.

Please Help.

 

[Ronald Dodge]

This is a case where you will then need to turn to VBA coding to help you
out in this process such as the following:

With Thisworkbook.Worksheets("Sheet1")
For I = lngFRW to lngLRW Step 1
Select Case VBA.UCase(.Range("X" & CStr(I)).Text)
Case "A"
.Range(CStr(I) & ":" & CStr(I)).Interior.ColorIndex = 1
Case "B"
.Range(CStr(I) & ":" & CStr(I)).Interior.ColorIndex = 2
Case "C"
.Range(CStr(I) & ":" & CStr(I)).Interior.ColorIndex = 3
Case "D"
.Range(CStr(I) & ":" & CStr(I)).Interior.ColorIndex = 4
Case "E"
.Range(CStr(I) & ":" & CStr(I)).Interior.ColorIndex = 5
. . . .
Case "X"
.Range(CStr(I) & ":" & CStr(I)).Interior.ColorIndex = 30
Case "Y"
.Range(CStr(I) & ":" & CStr(I)).Interior.ColorIndex = 31
Case "Z"
.Range(CStr(I) & ":" & CStr(I)).Interior.ColorIndex = 32
End Select
Next I
End With

For the color index codes, you may want to refer to:

http://www.mvps.org/dmcritchie/excel/colors.htm#palette

You will need to scroll on down until you see the different colors and it's
in order from 1 to 56. This does assume the default colors and that none of
the colors has been changed within the color tab of the Options dialog box.

 

[Carim]

Hi Gary,

Have a go with following example with 26 conditions ...

http://www.rhdatasolutions.com/ConditionalFormatVBA/CondFmtMoreThan3Cond.xls

HTH
Cheers
Carim

 

[Gary]

Hi Ronald,

I think this would work but am unable to figure out how.

am new to coding.

please help.

 

[Gary]

It doesnt explain anything.

 

[Aqib Rizvi]

It doesnt explain anything.

Try following steps, this does not require any VBA.

1. Make a table for results of all your possible conditions
2. in column 1 write expected results eg. A to J, and colum 2 write 1
against each
3. Give this table a name eg Table
4. Write a formula in your column 6, =VLOOKUP(E2,table,2,FALSE) E2 is
your column 5
5. If the result is according to your possible conditions, it will
return 1 in cell F2
6. Select cells A2:E2 and just one conditional format by selecting the
Formula =$F2=1 choose your highlighting colour in the condition

This will work.
Regards.
Aqib Rizvi

 

[Pete_UK]

Sorry, Gary, I can't help with this, and as Bob hasn't posted anything
for a couple of days I think he may have gone on holiday. I am sure he
would be grateful if you could contact him (via the xldynamic site) to
explain the problem you are having, and he'll get round to answering
you when he returns.

Pete

 

[Gord Dibben]

Gary

When you say you tried to open it do you mean you just double-clicked on it
after unzipping?

Best to just place it in your Office\Library folder then open Excel and go to
Tools>Add-ins and check CFPlus.

Then you may have further problems when xld Tools appears on your worksheeet
menu bar.

When you select "Launch CFPlus" you see message

"Error while preparing the workbook."

This is how we resolved another person's problem with CFPlus.

First of all, go to Tools>Options>SecurityMacro Security>Advanced>Trusted
Publishers.

Check "trust all installed add-ins and templates" and "trust access to visual
basic project".

OK out then click on Xld Tools and "Launch CFPlus"

You will get the error message. Click "Yes" to answer the question.

CFPlus should launch.


Gord Dibben MS Excel MVP

 

[Bob Phillips]

Isn't is just one condition with a formula of

=ISNUMBER(MATCH(A1,{"X","Y","Z","A","B","C","D","E","F","G"},0))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Gary]

Hi Bob,

Can you elaborate please?

Thanks
Gary

Isn't is just one condition with a formula of

=ISNUMBER(MATCH(A1,{"X","Y","Z","A","B","C","D","E","F","G"},0))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

I am saying that as I read it you don't have 10 criteria, just one big OR
criteria.

Of course, what I forgot was that you are using conditional formatting, and
you cannot use an array constant in CF, but if you put those values in a
range, say M1:M10, you could use CF with a formula of

=ISNUMBER(MATCH(A1,M1:M10,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Gary]

No Its not or.

Its like i have 7000 rows that i cant sort. I have names of people
(scattered) now what i wanna do is...rather than scrolling the entire sheet
and search that where all that name is..i just put a condition that wherever
the name is...the cell should be colored...rather that entire row should be
colored...so i need different colors.

Thanks

 

[Bob Phillips]

Did you look at Gord's suggestion on CFPlus.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)