Conditional Formatting - Lookup Range

Hello all, 

I have a spreadsheet with 79,000 cells of information in rows and
columns. On a separate worksheet I have 5,000 cells of information. 

I would like any information in the first sheet that is in the second
sheet to turn to red.

For example.  

Sheet1, column A
hellothere
howareyou
iamfine

Sheet2, column A
hellothere
iamfine

I would like the two strings in sheet one which appear in sheet 2 to
turn red, leaving the middle string black. 

Is there a way to do a lookup to compare the cell value to a range
using conditional formatting?

Thanks for any help.

Mxx


-- 
murphyz
------------------------------------------------------------------------
murphyz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20624
View this thread: http://www.excelforum.com/showthread.php?threadid=398508

0
8/24/2005 8:32:37 AM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
360 Views

Similar Articles

[PageSpeed] 28

There is.

Add a name for the range of data on sheet 2, say myRange

Select all the cells on Sheet1

Change the Condition 1 value in CF to Formula Is

Add a formula of =NOT(ISNA(MATCH(A1,myRange,0)))

Click Format, select pattern and choose red

OK out

-- 
 HTH

Bob Phillips

"murphyz" <murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com> wrote in
message news:murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com...
>
> Hello all,
>
> I have a spreadsheet with 79,000 cells of information in rows and
> columns. On a separate worksheet I have 5,000 cells of information.
>
> I would like any information in the first sheet that is in the second
> sheet to turn to red.
>
> For example.
>
> Sheet1, column A
> hellothere
> howareyou
> iamfine
>
> Sheet2, column A
> hellothere
> iamfine
>
> I would like the two strings in sheet one which appear in sheet 2 to
> turn red, leaving the middle string black.
>
> Is there a way to do a lookup to compare the cell value to a range
> using conditional formatting?
>
> Thanks for any help.
>
> Mxx
>
>
> -- 
> murphyz
> ------------------------------------------------------------------------
> murphyz's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=20624
> View this thread: http://www.excelforum.com/showthread.php?threadid=398508
>


0
bob.phillips1 (6510)
8/24/2005 10:24:33 AM
Try this code. Is it what you wanted to do? Sorry if I misunderstood
you.

Sub RedIfExist()

Dim rngCell As Range    'cells
Dim rngWork As Range    'working range
Dim lngLastRow  As Long     'last row
Dim lngLastRow1 As Long     'last row in 1st sheet
Dim ws2ndSheet  As Worksheet    '2nd worksheet
Dim lngRow  As Long     'row in 2nd sheet

'find last row in 1st sheet
lngLastRow1 = Cells(Rows.Count, "A").End(xlUp).Row

'"Sheet2" is the name of your 2nd sheet
Set ws2ndSheet = Sheets("Sheet2")
'Last row in 2nd sheet assuming column A always contains data
lngLastRow = ws2ndSheet.Cells(Rows.Count, "A").End(xlUp).Row

'Your 1st sheet date range
Set rngWork = Range("A1:A" & lngLastRow1)

'This will loop in working range
For Each rngCell In rngWork
'loop in the 2nd sheet until last row
For lngRow = 1 To lngLastRow
'If current cell value = column A in 2nd sheet value
'color red to the cell
If rngCell.Value = ws2ndSheet.Cells(lngRow, "A").Value Then
rngCell.Interior.ColorIndex = 3
End If
Next
Next

End Sub


-- 
hideki
------------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18903
View this thread: http://www.excelforum.com/showthread.php?threadid=398508

0
8/24/2005 10:50:41 AM
Bob,
Does Match() only work where you are comparing 1 column (sheet1) against 
another single column(sheet2).  I tried entering data on sheet2 in columns 
b, c, and d (also including in my "myrange"), but nothing turns red now on 
my sheet1.
TIA,
Jim

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:uF%23$SYJqFHA.3108@TK2MSFTNGP12.phx.gbl...
> There is.
>
> Add a name for the range of data on sheet 2, say myRange
>
> Select all the cells on Sheet1
>
> Change the Condition 1 value in CF to Formula Is
>
> Add a formula of =NOT(ISNA(MATCH(A1,myRange,0)))
>
> Click Format, select pattern and choose red
>
> OK out
>
> -- 
> HTH
>
> Bob Phillips
>
> "murphyz" <murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com> wrote in
> message news:murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com...
>>
>> Hello all,
>>
>> I have a spreadsheet with 79,000 cells of information in rows and
>> columns. On a separate worksheet I have 5,000 cells of information.
>>
>> I would like any information in the first sheet that is in the second
>> sheet to turn to red.
>>
>> For example.
>>
>> Sheet1, column A
>> hellothere
>> howareyou
>> iamfine
>>
>> Sheet2, column A
>> hellothere
>> iamfine
>>
>> I would like the two strings in sheet one which appear in sheet 2 to
>> turn red, leaving the middle string black.
>>
>> Is there a way to do a lookup to compare the cell value to a range
>> using conditional formatting?
>>
>> Thanks for any help.
>>
>> Mxx
>>
>>
>> -- 
>> murphyz
>> ------------------------------------------------------------------------
>> murphyz's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=20624
>> View this thread: 
>> http://www.excelforum.com/showthread.php?threadid=398508
>>
>
> 


0
jmay (696)
8/24/2005 11:16:28 AM
Hi Jim,

Yes, MATCH is single column/row, it returns an index into that array, so if
it were multi-column and row, it would need to return two values.

-- 
 HTH

Bob Phillips

"Jim May" <jmay@cox.net> wrote in message news:iGYOe.432$8q.32@lakeread01...
> Bob,
> Does Match() only work where you are comparing 1 column (sheet1) against
> another single column(sheet2).  I tried entering data on sheet2 in columns
> b, c, and d (also including in my "myrange"), but nothing turns red now on
> my sheet1.
> TIA,
> Jim
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:uF%23$SYJqFHA.3108@TK2MSFTNGP12.phx.gbl...
> > There is.
> >
> > Add a name for the range of data on sheet 2, say myRange
> >
> > Select all the cells on Sheet1
> >
> > Change the Condition 1 value in CF to Formula Is
> >
> > Add a formula of =NOT(ISNA(MATCH(A1,myRange,0)))
> >
> > Click Format, select pattern and choose red
> >
> > OK out
> >
> > -- 
> > HTH
> >
> > Bob Phillips
> >
> > "murphyz" <murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com> wrote
in
> > message news:murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com...
> >>
> >> Hello all,
> >>
> >> I have a spreadsheet with 79,000 cells of information in rows and
> >> columns. On a separate worksheet I have 5,000 cells of information.
> >>
> >> I would like any information in the first sheet that is in the second
> >> sheet to turn to red.
> >>
> >> For example.
> >>
> >> Sheet1, column A
> >> hellothere
> >> howareyou
> >> iamfine
> >>
> >> Sheet2, column A
> >> hellothere
> >> iamfine
> >>
> >> I would like the two strings in sheet one which appear in sheet 2 to
> >> turn red, leaving the middle string black.
> >>
> >> Is there a way to do a lookup to compare the cell value to a range
> >> using conditional formatting?
> >>
> >> Thanks for any help.
> >>
> >> Mxx
> >>
> >>
> >> -- 
> >> murphyz
>
>> ------------------------------------------------------------------------
> >> murphyz's Profile:
> > http://www.excelforum.com/member.php?action=getinfo&userid=20624
> >> View this thread:
> >> http://www.excelforum.com/showthread.php?threadid=398508
> >>
> >
> >
>
>


0
bob.phillips1 (6510)
8/24/2005 11:23:14 AM
"so if it were multi-column and row, it would need to return two values."  < 
WHICH IT CAN'T !! Right?
Sorry to be so thick (here),,
Appreciate your contribution to this (an other excel) group(s).
Jim

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:%23qCsN5JqFHA.1272@TK2MSFTNGP11.phx.gbl...
> Hi Jim,
>
> Yes, MATCH is single column/row, it returns an index into that array, so 
> if
> it were multi-column and row, it would need to return two values.
>
> -- 
> HTH
>
> Bob Phillips
>
> "Jim May" <jmay@cox.net> wrote in message 
> news:iGYOe.432$8q.32@lakeread01...
>> Bob,
>> Does Match() only work where you are comparing 1 column (sheet1) against
>> another single column(sheet2).  I tried entering data on sheet2 in 
>> columns
>> b, c, and d (also including in my "myrange"), but nothing turns red now 
>> on
>> my sheet1.
>> TIA,
>> Jim
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:uF%23$SYJqFHA.3108@TK2MSFTNGP12.phx.gbl...
>> > There is.
>> >
>> > Add a name for the range of data on sheet 2, say myRange
>> >
>> > Select all the cells on Sheet1
>> >
>> > Change the Condition 1 value in CF to Formula Is
>> >
>> > Add a formula of =NOT(ISNA(MATCH(A1,myRange,0)))
>> >
>> > Click Format, select pattern and choose red
>> >
>> > OK out
>> >
>> > -- 
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "murphyz" <murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com> wrote
> in
>> > message news:murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com...
>> >>
>> >> Hello all,
>> >>
>> >> I have a spreadsheet with 79,000 cells of information in rows and
>> >> columns. On a separate worksheet I have 5,000 cells of information.
>> >>
>> >> I would like any information in the first sheet that is in the second
>> >> sheet to turn to red.
>> >>
>> >> For example.
>> >>
>> >> Sheet1, column A
>> >> hellothere
>> >> howareyou
>> >> iamfine
>> >>
>> >> Sheet2, column A
>> >> hellothere
>> >> iamfine
>> >>
>> >> I would like the two strings in sheet one which appear in sheet 2 to
>> >> turn red, leaving the middle string black.
>> >>
>> >> Is there a way to do a lookup to compare the cell value to a range
>> >> using conditional formatting?
>> >>
>> >> Thanks for any help.
>> >>
>> >> Mxx
>> >>
>> >>
>> >> -- 
>> >> murphyz
>>
>>> ------------------------------------------------------------------------
>> >> murphyz's Profile:
>> > http://www.excelforum.com/member.php?action=getinfo&userid=20624
>> >> View this thread:
>> >> http://www.excelforum.com/showthread.php?threadid=398508
>> >>
>> >
>> >
>>
>>
>
> 


0
jmay (696)
8/24/2005 11:44:29 AM
I hesitate to say it can't (or more accurately couldn't), but it doesn't.

Bob

"Jim May" <jmay@cox.net> wrote in message
news:z4ZOe.434$8q.266@lakeread01...
> "so if it were multi-column and row, it would need to return two values."
<
> WHICH IT CAN'T !! Right?
> Sorry to be so thick (here),,
> Appreciate your contribution to this (an other excel) group(s).
> Jim
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23qCsN5JqFHA.1272@TK2MSFTNGP11.phx.gbl...
> > Hi Jim,
> >
> > Yes, MATCH is single column/row, it returns an index into that array, so
> > if
> > it were multi-column and row, it would need to return two values.
> >
> > -- 
> > HTH
> >
> > Bob Phillips
> >
> > "Jim May" <jmay@cox.net> wrote in message
> > news:iGYOe.432$8q.32@lakeread01...
> >> Bob,
> >> Does Match() only work where you are comparing 1 column (sheet1)
against
> >> another single column(sheet2).  I tried entering data on sheet2 in
> >> columns
> >> b, c, and d (also including in my "myrange"), but nothing turns red now
> >> on
> >> my sheet1.
> >> TIA,
> >> Jim
> >>
> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> >> news:uF%23$SYJqFHA.3108@TK2MSFTNGP12.phx.gbl...
> >> > There is.
> >> >
> >> > Add a name for the range of data on sheet 2, say myRange
> >> >
> >> > Select all the cells on Sheet1
> >> >
> >> > Change the Condition 1 value in CF to Formula Is
> >> >
> >> > Add a formula of =NOT(ISNA(MATCH(A1,myRange,0)))
> >> >
> >> > Click Format, select pattern and choose red
> >> >
> >> > OK out
> >> >
> >> > -- 
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > "murphyz" <murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com>
wrote
> > in
> >> > message news:murphyz.1u9gyf_1124874312.9357@excelforum-nospam.com...
> >> >>
> >> >> Hello all,
> >> >>
> >> >> I have a spreadsheet with 79,000 cells of information in rows and
> >> >> columns. On a separate worksheet I have 5,000 cells of information.
> >> >>
> >> >> I would like any information in the first sheet that is in the
second
> >> >> sheet to turn to red.
> >> >>
> >> >> For example.
> >> >>
> >> >> Sheet1, column A
> >> >> hellothere
> >> >> howareyou
> >> >> iamfine
> >> >>
> >> >> Sheet2, column A
> >> >> hellothere
> >> >> iamfine
> >> >>
> >> >> I would like the two strings in sheet one which appear in sheet 2 to
> >> >> turn red, leaving the middle string black.
> >> >>
> >> >> Is there a way to do a lookup to compare the cell value to a range
> >> >> using conditional formatting?
> >> >>
> >> >> Thanks for any help.
> >> >>
> >> >> Mxx
> >> >>
> >> >>
> >> >> -- 
> >> >> murphyz
> >>
>
>>> ------------------------------------------------------------------------
> >> >> murphyz's Profile:
> >> > http://www.excelforum.com/member.php?action=getinfo&userid=20624
> >> >> View this thread:
> >> >> http://www.excelforum.com/showthread.php?threadid=398508
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>


0
bob.phillips1 (6510)
8/24/2005 11:55:24 AM
Another way:

Select the range of cells that contain the "master list".  Give it a nice range
name (Insert|Name|Define).  I used myRng.

Then back to the other sheet that should turn colors.

Select your range (and with A1 the active cell):

format|conditional formatting
Formula is:  =COUNTIF(myRng,A1)>0
And give it a nice format (from the pattern tab???)



murphyz wrote:
> 
> Hello all,
> 
> I have a spreadsheet with 79,000 cells of information in rows and
> columns. On a separate worksheet I have 5,000 cells of information.
> 
> I would like any information in the first sheet that is in the second
> sheet to turn to red.
> 
> For example.
> 
> Sheet1, column A
> hellothere
> howareyou
> iamfine
> 
> Sheet2, column A
> hellothere
> iamfine
> 
> I would like the two strings in sheet one which appear in sheet 2 to
> turn red, leaving the middle string black.
> 
> Is there a way to do a lookup to compare the cell value to a range
> using conditional formatting?
> 
> Thanks for any help.
> 
> Mxx
> 
> --
> murphyz
> ------------------------------------------------------------------------
> murphyz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20624
> View this thread: http://www.excelforum.com/showthread.php?threadid=398508

-- 

Dave Peterson
0
petersod (12005)
8/24/2005 1:51:44 PM
Thanks for all the answers.

I went with the way that Bob had mentioned and that worked fine and
dandy for what I needed. 

Much appreciated for all who answered though. 

Mxx


-- 
murphyz
------------------------------------------------------------------------
murphyz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20624
View this thread: http://www.excelforum.com/showthread.php?threadid=398508

0
8/24/2005 2:55:50 PM
Reply:

Similar Artilces: