Prev: update vlookup formula sheet reference for multiple sheets
Next: VBA data validation
From: Wendy on 8 May 2010 01:46
I want to be able to colour code the name of jo bloggs when his name apprears
in a cell that has many names. I have been able to colour code a range of
cells with a few names but I have a list to 200 names that I need to check
against the same listing of names.

I used conditional format but need to be able to apply this to a large of
names against a set of data that holds many names.


Wendy
From: Jacob Skaria on 8 May 2010 02:06
Hi Wendy

If you want to color the names in ColA if they belong to the subset of names
in ColB; try the below formula

1. Select the cell/Range (say A1:A100). Please note that the cell reference
A1 mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=COUNTIF($B$1:$B$10,A1)
4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format. Enter the formula in the box below.


Col A Col B
Name1 Name4
Name2 Name6
Name3
Name4
Name5
Name6
Name7
Name8
Name9
Name10
Name11
Name12


--
Jacob (MVP - Excel)


"Wendy" wrote:

> I want to be able to colour code the name of jo bloggs when his name apprears
> in a cell that has many names. I have been able to colour code a range of
> cells with a few names but I have a list to 200 names that I need to check
> against the same listing of names.
>
> I used conditional format but need to be able to apply this to a large of
> names against a set of data that holds many names.
>
>
> Wendy
From: Ashish Mathur on 8 May 2010 03:11
Hi,

Let's say the names are in A3:A50. The names which you want to colour are
in B5:B500. While on cell B3, go to Home > Styles > Conditional formatting
> New Rule > Use a formula to determine which cells to format and in the
box, there, type the following formula

B3=vlookup(B3,A$3:A$50,1,0). Select the desired format and click on OK

To copy and paste conditional formatting, copy cell A3, Edit > Paste Special
> Formats

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wendy" <Wendy(a)discussions.microsoft.com> wrote in message
news:57F4BBEF-AF57-40F6-9FF1-652A0A1BED3F(a)microsoft.com...
> I want to be able to colour code the name of jo bloggs when his name
> apprears
> in a cell that has many names. I have been able to colour code a range of
> cells with a few names but I have a list to 200 names that I need to check
> against the same listing of names.
>
> I used conditional format but need to be able to apply this to a large of
> names against a set of data that holds many names.
>
>
> Wendy

From: Wendy on 8 May 2010 05:45
Thankyou

If the list I am checking has a more than one name in the same cell, , and I
want to check against if the name appears in the list - how can I do that?
--
Wendy


"Ashish Mathur" wrote:

> Hi,
>
> Let's say the names are in A3:A50. The names which you want to colour are
> in B5:B500. While on cell B3, go to Home > Styles > Conditional formatting
> > New Rule > Use a formula to determine which cells to format and in the
> box, there, type the following formula
>
> B3=vlookup(B3,A$3:A$50,1,0). Select the desired format and click on OK
>
> To copy and paste conditional formatting, copy cell A3, Edit > Paste Special
> > Formats
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
>
> "Wendy" <Wendy(a)discussions.microsoft.com> wrote in message
> news:57F4BBEF-AF57-40F6-9FF1-652A0A1BED3F(a)microsoft.com...
> > I want to be able to colour code the name of jo bloggs when his name
> > apprears
> > in a cell that has many names. I have been able to colour code a range of
> > cells with a few names but I have a list to 200 names that I need to check
> > against the same listing of names.
> >
> > I used conditional format but need to be able to apply this to a large of
> > names against a set of data that holds many names.
> >
> >
> > Wendy
>
> .
>
From: Ms-Exl-Learner on 8 May 2010 12:06
Have a look in the below links for highlighting the duplicate values using
Conditional Formatting.

http://office.microsoft.com/en-us/excel/ha011366161033.aspx
http://www.contextures.com/xlcondformat03.html

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Wendy" wrote:

> Thankyou
>
> If the list I am checking has a more than one name in the same cell, , and I
> want to check against if the name appears in the list - how can I do that?
> --
> Wendy
>
>
> "Ashish Mathur" wrote:
>
> > Hi,
> >
> > Let's say the names are in A3:A50. The names which you want to colour are
> > in B5:B500. While on cell B3, go to Home > Styles > Conditional formatting
> > > New Rule > Use a formula to determine which cells to format and in the
> > box, there, type the following formula
> >
> > B3=vlookup(B3,A$3:A$50,1,0). Select the desired format and click on OK
> >
> > To copy and paste conditional formatting, copy cell A3, Edit > Paste Special
> > > Formats
> >
> > --
> > Regards,
> >
> > Ashish Mathur
> > Microsoft Excel MVP
> > www.ashishmathur.com
> >
> > "Wendy" <Wendy(a)discussions.microsoft.com> wrote in message
> > news:57F4BBEF-AF57-40F6-9FF1-652A0A1BED3F(a)microsoft.com...
> > > I want to be able to colour code the name of jo bloggs when his name
> > > apprears
> > > in a cell that has many names. I have been able to colour code a range of
> > > cells with a few names but I have a list to 200 names that I need to check
> > > against the same listing of names.
> > >
> > > I used conditional format but need to be able to apply this to a large of
> > > names against a set of data that holds many names.
> > >
> > >
> > > Wendy
> >
> > .
> >
 |  Next  |  Last
Pages: 1 2
Prev: update vlookup formula sheet reference for multiple sheets
Next: VBA data validation