Prev: Reference to values disappear after closing file
Next: Showing the Differences
From: Pam on 24 Feb 2010 17:59
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


From: Russell Dawson on 24 Feb 2010 22:04
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
>
>
> .
>
From: Pam on 25 Feb 2010 09:22
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" <russell.dawson(a)discussions.microsoft.com> wrote in message
news:E90D5D7F-8FCF-40CD-B96A-EDD38223D910(a)microsoft.com...
> 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
>>
>>
>> .
>>


From: Hubisan on 25 Feb 2010 10:11
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



From: Russell Dawson on 25 Feb 2010 10:19
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" <russell.dawson(a)discussions.microsoft.com> wrote in message
> news:E90D5D7F-8FCF-40CD-B96A-EDD38223D910(a)microsoft.com...
> > 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
> >>
> >>
> >> .
> >>
>
>
> .
>
 |  Next  |  Last
Pages: 1 2 3
Prev: Reference to values disappear after closing file
Next: Showing the Differences