Conditional Formatting - Lookup Range

[murphyz]

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

 

[Bob Phillips]

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

 

[hideki]

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

 

[Jim May]

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]

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.

 

[Jim May]

"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]

I hesitate to say it can't (or more accurately couldn't), but it doesn't.

Bob

 

[Dave Peterson]

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]

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