|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
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 wrote:
[Quoted Text] > 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
|
|
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" <pashurst[ at ]auditel.net> wrote in message
news:1159562961.760947.30750[ at ]k70g2000cwa.googlegroups.com...
[Quoted Text] > 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 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 >
|
|
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" <gaurav_ss[ at ]hotmail.com> wrote in message
news:eGli3eA5GHA.3836[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > 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
>
>
|
|
|
|
Hi Ronald,
I think this would work but am unable to figure out how.
am new to coding.
please help.
"Ronald Dodge" <ronald.dodge[ at ]cfgraphics.com> wrote in message
news:O8eWYxA5GHA.3444[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > 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" <gaurav_ss[ at ]hotmail.com> wrote in message > news:eGli3eA5GHA.3836[ at ]TK2MSFTNGP06.phx.gbl... >> 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 >> >> > >
|
|
It doesnt explain anything.
"Carim" <carimfam[ at ]yahoo.com> wrote in message
news:1159564555.002703.67570[ at ]b28g2000cwb.googlegroups.com...
[Quoted Text]
|
|
Gary wrote:
[Quoted Text]
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
|
|
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:
[Quoted Text] > 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" <pashurst[ at ]auditel.net> wrote in message > news:1159562961.760947.30750[ at ]k70g2000cwa.googlegroups.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.CFPlus.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 > >
|
|
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
On 29 Sep 2006 17:11:13 -0700, "Pete_UK" <pashurst[ at ]auditel.net> wrote:
[Quoted Text] >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" <pashurst[ at ]auditel.net> wrote in message >> news:1159562961.760947.30750[ at ]k70g2000cwa.googlegroups.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.CFPlus.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 >> >
|
|
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" <gaurav_ss[ at ]hotmail.com> wrote in message
news:eGli3eA5GHA.3836[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > 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
>
>
|
|
|