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" <(E-Mail Removed)> wrote in message
news:E90D5D7F-8FCF-40CD-B96A-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:8F42C7C8-42E9-46DC-8420-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
>> message
>> news:E90D5D7F-8FCF-40CD-B96A-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
news:dc2c6467-307a-4455-9db8-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:dc2c6467-307a-4455-9db8-(E-Mail Removed)...
> 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:c8cfbbae-4704-4b39-9e11-(E-Mail Removed)...
> 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
>
>