Colour Cell When Alternative Is Found

[Dazzawm]

I have sheet 5 with a list of numbers in column A & B. I need a macro please that whenever I write or paste a number in column AE on sheet1 that is on sheet 5 in column A then to colour the cell in AE.

So if I copy or paste NS606292 into column AE on sheet 1 there is a number next to it in column B - CIR25000P so NS606292 needs to be coloured on sheet 1. If I enter a number in AE on sheet 1 and it is not in column A on sheet 5 then don't colour. Hope I have explained ok. Thanks.

NS606292	CIR25000P
NS606087	CIR8000P
NS617852	CR10000S

 

[rollis13]

You could apply in Conditional Formatting of column AE of Sheet1 the formula:
=COUNTIF(Sheet5!A:A,AE1)

 

[Dazzawm]

You could apply in Conditional Formatting of column AE of Sheet1 the formula:
=COUNTIF(Sheet5!A:A,AE1)

Thanks but I really need a macro to use on multiple files with many columns and many thousands of rows.

 

[rollis13]

Just adapt the formula range in every different file or ... maybe it's me not understanding your request in post #1 because there the criteria was only Sheet5 vs Sheet1.

 

[Dazzawm]

Just adapt the formula range in every different file or ... maybe it's me not understanding your request in post #1 because there the criteria was only Sheet5 vs Sheet1.

I'll wait to see if someone can help with my actual request.

 

[Dazzawm]

Can anyone help with a code for this please?

 

[Dazzawm]

Looks like I've lucked out here.

 

[Dazzawm]

One last call if anyone can help please?

 

[Dazzawm]

Don't worry folks I found one using AI, below is the code if you're interested.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws1 As Worksheet
    Dim ws5 As Worksheet
    Dim cell As Range
    Dim lookupRange As Range
    Dim foundCell As Range
   
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws5 = ThisWorkbook.Sheets("Sheet5")
   
    ' Check if the changed cell is in column AE of Sheet1
    If Not Intersect(Target, ws1.Range("AE:AE")) Is Nothing Then
        Application.EnableEvents = False
        ' Loop through each cell in column A of Sheet5 to find a match
        For Each cell In ws5.Range("A1:A" & ws5.Cells(ws5.Rows.Count, "A").End(xlUp).Row)
            If StrComp(cell.Value, Target.Value, vbTextCompare) = 0 Then
                ' If match is found, color the corresponding cell in column AE of Sheet1
                Set lookupRange = ws5.Range("A1:B" & ws5.Cells(ws5.Rows.Count, "A").End(xlUp).Row)
                Set foundCell = lookupRange.Find(Target.Value, LookIn:=xlValues, LookAt:=xlWhole)
                If Not foundCell Is Nothing Then
                    ws1.Range("AE" & Target.Row).Interior.Color = RGB(255, 255, 0) ' Change color as needed
                End If
                Exit For
            End If
        Next cell
        Application.EnableEvents = True
    End If
End Sub

 

[rollis13]

So what's the difference with my Conditional Formatting formula in post #2 ? Have you tried deleting an input in the AE column ? Does the macro behave as you wish ?
Instead, have a try with my CF only and see if it behaves smarter (this would be my level of AI though).