Hi,

I have a spreadsheet with Department Column and Class Column. I need to
highlight the department cell when it is a 5 and three-digit class code ends
in anything other than "P". Can this be done in code? I know I can't use
wildcards for "**P" in conditional formatting.

I would appreciate any help.
Thanks,
Pam

In my example the headers are in G10 (Dept) and H10 (Class)
In 2007
Select g11
Cond formatting - manage rules - new rule - use formula to determine . . -
enter following in rule description
=ISNUMBER(SEARCH("P",H11))+G11=5
Format as required - fill cell blue or whatever
Ok
Obviously you will need to change the object cells to suit and use format
painter to rest of range.

Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Pam" wrote:

Hi,

I have a spreadsheet with Department Column and Class Column. I need to
highlight the department cell when it is a 5 and three-digit class code ends
in anything other than "P". Can this be done in code? I know I can't use
wildcards for "**P" in conditional formatting.

I would appreciate any help.
Thanks,
Pam


.

Russell,

I need to search the three digit code for anything ending other than "P". I
tried your solution to see if I could make it work and then try to modify as
needed, but could not get it to work.

Thanks,
Pam

"Russell Dawson" wrote in message
...
In my example the headers are in G10 (Dept) and H10 (Class)
In 2007
Select g11
Cond formatting - manage rules - new rule - use formula to determine .
-
enter following in rule description
=ISNUMBER(SEARCH("P",H11))+G11=5
Format as required - fill cell blue or whatever
Ok
Obviously you will need to change the object cells to suit and use format
painter to rest of range.

Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Pam" wrote:

Hi,

I have a spreadsheet with Department Column and Class Column. I need to
highlight the department cell when it is a 5 and three-digit class code
ends
in anything other than "P". Can this be done in code? I know I can't
use
wildcards for "**P" in conditional formatting.

I would appreciate any help.
Thanks,
Pam


.

Use this formula as conditional formatting:

=AND(RIGHT(H11,1)<"P";G11=5)

And btw, u can use wildcards whith some formulas like
"=Search("*P",H11)"

Hubisan

Hi Pam

If I understand correctly then, if the cells contain 5 & 22a then it is
highlighted - if 5 & 22p no highlight
- if 6 & 22a - no highlight.

You only described your "3 digit class code". What does it look like exactly.

My example works fine but we are obviously missing something. It will work,
just a bit of fine tuning I'm sure.

Regards
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Pam" wrote:

Russell,

I need to search the three digit code for anything ending other than "P". I
tried your solution to see if I could make it work and then try to modify as
needed, but could not get it to work.

Thanks,
Pam

"Russell Dawson" wrote in message
...
In my example the headers are in G10 (Dept) and H10 (Class)
In 2007
Select g11
Cond formatting - manage rules - new rule - use formula to determine .
-
enter following in rule description
=ISNUMBER(SEARCH("P",H11))+G11=5
Format as required - fill cell blue or whatever
Ok
Obviously you will need to change the object cells to suit and use format
painter to rest of range.

Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Pam" wrote:

Hi,

I have a spreadsheet with Department Column and Class Column. I need to
highlight the department cell when it is a 5 and three-digit class code
ends
in anything other than "P". Can this be done in code? I know I can't
use
wildcards for "**P" in conditional formatting.

I would appreciate any help.
Thanks,
Pam


.



.

Russell,

Here's what I need to happen:
ColB ColC
Dept Class
5 GRR Highlight
5 GRP
3 GRR
5 GRU Highlight
1 GRU

If Dept 5 has code ending in anything other than "P" - highlight it.

Thanks again for your help.
Pam

"Russell Dawson" wrote in message
...
Hi Pam

If I understand correctly then, if the cells contain 5 & 22a then it is
highlighted - if 5 & 22p no highlight
- if 6 & 22a - no highlight.

You only described your "3 digit class code". What does it look like
exactly.

My example works fine but we are obviously missing something. It will
work,
just a bit of fine tuning I'm sure.

Regards
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Pam" wrote:

Russell,

I need to search the three digit code for anything ending other than "P".
I
tried your solution to see if I could make it work and then try to modify
as
needed, but could not get it to work.

Thanks,
Pam

"Russell Dawson" wrote in
message
...
In my example the headers are in G10 (Dept) and H10 (Class)
In 2007
Select g11
Cond formatting - manage rules - new rule - use formula to determine .
-
enter following in rule description
=ISNUMBER(SEARCH("P",H11))+G11=5
Format as required - fill cell blue or whatever
Ok
Obviously you will need to change the object cells to suit and use
format
painter to rest of range.

Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Pam" wrote:

Hi,

I have a spreadsheet with Department Column and Class Column. I need
to
highlight the department cell when it is a 5 and three-digit class
code
ends
in anything other than "P". Can this be done in code? I know I can't
use
wildcards for "**P" in conditional formatting.

I would appreciate any help.
Thanks,
Pam


.



.

Hubisan,

This looks like it will work, but it give error message "The formula you
typed contains an error." and highlights "P" in the formula.

Thanks for your help.
Pam


"Hubisan" wrote in message
...
Use this formula as conditional formatting:

=AND(RIGHT(H11,1)<"P";G11=5)

And btw, u can use wildcards whith some formulas like
"=Search("*P",H11)"

Hubisan

Hubisan,

I changed the semi-colon to a comma and it works. Can you tell me what the
"1" is for in (H11,1)?

Thank you.
Pam

"Hubisan" wrote in message
...
Use this formula as conditional formatting:

=AND(RIGHT(H11,1)<"P";G11=5)

And btw, u can use wildcards whith some formulas like
"=Search("*P",H11)"

Hubisan

I see now where we had our wires crossed. You'd said 3 digit whereas it was
three characters. I then assumed the last char was the P or whatever.

Don't forget to give Hubisan a "Yes" for solving the problem.

Regards
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Pam" wrote:

Russell,

Here's what I need to happen:
ColB ColC
Dept Class
5 GRR Highlight
5 GRP
3 GRR
5 GRU Highlight
1 GRU

If Dept 5 has code ending in anything other than "P" - highlight it.

Thanks again for your help.
Pam

"Russell Dawson" wrote in message
...
Hi Pam

If I understand correctly then, if the cells contain 5 & 22a then it is
highlighted - if 5 & 22p no highlight
- if 6 & 22a - no highlight.

You only described your "3 digit class code". What does it look like
exactly.

My example works fine but we are obviously missing something. It will
work,
just a bit of fine tuning I'm sure.

Regards
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Pam" wrote:

Russell,

I need to search the three digit code for anything ending other than "P".
I
tried your solution to see if I could make it work and then try to modify
as
needed, but could not get it to work.

Thanks,
Pam

"Russell Dawson" wrote in
message
...
In my example the headers are in G10 (Dept) and H10 (Class)
In 2007
Select g11
Cond formatting - manage rules - new rule - use formula to determine .
-
enter following in rule description
=ISNUMBER(SEARCH("P",H11))+G11=5
Format as required - fill cell blue or whatever
Ok
Obviously you will need to change the object cells to suit and use
format
painter to rest of range.

Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Pam" wrote:

Hi,

I have a spreadsheet with Department Column and Class Column. I need
to
highlight the department cell when it is a 5 and three-digit class
code
ends
in anything other than "P". Can this be done in code? I know I can't
use
wildcards for "**P" in conditional formatting.

I would appreciate any help.
Thanks,
Pam


.



.



.

Ah, sorry for the mistake, using swiss german excel and we use ";"
instead of ","

Right( text, number_of_characters )

text is the string that you wish to extract from.

number_of_characters indicates the number of characters that you wish to extract starting from the right-most character.

and Russells formula would work as well, just add wildcards:
=ISNUMBER(SEARCH("??P",H11))+G11=5

Hubisan,

I'm sorry, I should have known that. Thank you for the formula and info you
supplied. One more thing, can this be written so that I can use it in vba
code?

Thanks again,
Pam

"Hubisan" wrote in message
...
Ah, sorry for the mistake, using swiss german excel and we use ";"
instead of ","

Right( text, number_of_characters )

text is the string that you wish to extract from.

number_of_characters indicates the number of characters that you wish to
extract starting from the right-most character.

and Russells formula would work as well, just add wildcards:
=ISNUMBER(SEARCH("??P",H11))+G11=5