Something more than Conditional Formatting
 

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

Something more than Conditional Formatting
 

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

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

Hope this helps.

Pete

Gary wrote:
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

Something more than Conditional Formatting
 

Thanks Pete,

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

Runtime error 9
Subscript Out Of Range.

Please Help.

"Pete_UK" wrote in message
ups.com...
You could download this free Add-in from Bob Phillips - it gives you up
to 30 conditional formats:

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

Hope this helps.

Pete

Gary wrote:
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

Something more than Conditional Formatting
 

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.

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

"Gary" wrote in message
...
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

Something more than Conditional Formatting
 

Hi Gary,

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

http://www.rhdatasolutions.com/Condi...eThan3Cond.xls

HTH
Cheers
Carim

Something more than Conditional Formatting
 

Hi Ronald,

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

am new to coding.

please help.

"Ronald Dodge" wrote in message
...
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.

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

"Gary" wrote in message
...
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

Something more than Conditional Formatting
 

It doesnt explain anything.

"Carim" wrote in message
ups.com...
Hi Gary,

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

http://www.rhdatasolutions.com/Condi...eThan3Cond.xls

HTH
Cheers
Carim

Something more than Conditional Formatting
 

Gary wrote:
It doesnt explain anything.

"Carim" wrote in message
ups.com...
Hi Gary,

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

http://www.rhdatasolutions.com/Condi...eThan3Cond.xls

HTH
Cheers
Carim



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

Something more than Conditional Formatting
 

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

Gary wrote:
Thanks Pete,

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

Runtime error 9
Subscript Out Of Range.

Please Help.

"Pete_UK" wrote in message
ups.com...
You could download this free Add-in from Bob Phillips - it gives you up
to 30 conditional formats:

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

Hope this helps.

Pete

Gary wrote:
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

Something more than Conditional Formatting
 

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
ToolsAdd-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 ToolsOptionsSecurityMacro SecurityAdvancedTrusted
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

On 29 Sep 2006 17:11:13 -0700, "Pete_UK" wrote:

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

Gary wrote:
Thanks Pete,

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

Runtime error 9
Subscript Out Of Range.

Please Help.

"Pete_UK" wrote in message
ups.com...
You could download this free Add-in from Bob Phillips - it gives you up
to 30 conditional formats:

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

Hope this helps.

Pete

Gary wrote:
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

Something more than Conditional Formatting
 

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" wrote in message
...
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

Something more than Conditional Formatting
 

Hi Bob,

Can you elaborate please?

Thanks
Gary

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

Something more than Conditional Formatting
 

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" wrote in message
...
Hi Bob,

Can you elaborate please?

Thanks
Gary

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

Something more than Conditional Formatting
 

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" wrote in message
...
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" wrote in message
...
Hi Bob,

Can you elaborate please?

Thanks
Gary

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

Something more than Conditional Formatting
 

Did you look at Gord's suggestion on CFPlus.

--
HTH

Bob Phillips

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

"Gary" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Hi Bob,

Can you elaborate please?

Thanks
Gary

"Bob Phillips" wrote in message
...
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" wrote in message
...
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