VBA Code for highlighting cells if it appears elsewhere

Guys,

Need a bit of code help. I want to change the colour of a cell in column A of sheet 2, if it appears in column C of sheet 1.

Can any VB smart folks out there help out?

Cheers.

Try this:

Sub EF974522_find()
    Dim c, f As Range
    
    For Each c In Sheets("Sheet2").Range("A1", Sheets("Sheet2").Range("A" & rows.Count).End(xlUp))
        Set f = Sheets("Sheet1").Columns(3).Find(c.Value, lookat:=xlWhole)
        If Not f Is Nothing Then c.Interior.Color = 255
    Next
End Sub

Hi Millz, it seems to work but if the cell in sheet 1 is removed, the formatting in sheet 2 remains? Is there a way that if the number in sheet 1 is removed it will revert back to the original formatting i.e. un-highlighted? cheers

Do you want it reverted automatically or by running the macro again?

If running the macro a second time is fine, simply add this:

Sub EF974522_find()
    Dim c, f As Range
    
    Sheets("Sheet2").Columns(1).Interior.Pattern = xlNone
    For Each c In Sheets("Sheet2").Range("A1", Sheets("Sheet2").Range("A" & rows.Count).End(xlUp))
        Set f = Sheets("Sheet1").Columns(3).Find(c.Value, lookat:=xlWhole)
        If Not f Is Nothing Then c.Interior.Color = 255
    Next
End Sub

Thats works great thanks. My first real exposure to this, so apologies if a silly question, but rather than associating it to a macro and running that, is there a way to have it always running on the workbook?

Cheers either way!

Place this in Sheet1's code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns(3)) Is Nothing Then Call EF974522_find
End Sub

Place this in Sheet2's code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns(1)) Is Nothing Then Call EF974522_find
End Sub

and then the macro in a module:

Sub EF974522_find()
    Dim c, f As Range
    
    Sheets("Sheet2").Columns(1).Interior.Pattern = xlNone
    For Each c In Sheets("Sheet2").Range("A1", Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp))
        Set f = Sheets("Sheet1").Columns(3).Find(c.Value, lookat:=xlWhole)
        If Not f Is Nothing Then c.Interior.Color = 255
    Next
End Sub

The first 2 code will then trigger the macro whenever data is changed in those specific columns.

Also note that doing it this way will also make your workbook become macro-enabled workbook, and will require you to allow macros to run before it would work.

Originally Posted by mpgates

I want to change the colour of a cell in column A of sheet 2, if it appears in column C of sheet 1.

Why not conditional formatting?

Thanks Millz, works a treat.