[Solved] How to convert a conditional formatting to VBA Code??

[bino1121]

I need to convert this into VBA code.... The range I have set there isn't really 1048576 I just couldn't figure out how to set it as control+shift+down. and the cells that this applies to is from $A$4:$A$1048576

 

[Lutfor Rahman Shimanto]

View attachment 321

I need to convert this into VBA code.... The range I have set there isn't really 1048576 I just couldn't figure out how to set it as control+shift+down. and the cells that this applies to is from $A$4:$A$1048576

Hello Bino1121

Thanks for reaching out and posting your query. By analyzing the formula you mentioned to apply Conditional Formatting, I understand that the overall formula returns TRUE if the value in cell A2 is found in the range Sheet2!A4:A1048576 and FALSE if not found. As you requested, I implemented an Excel VBA code to fill your goal. This code will compare the value in cell A2 of Sheet1 with each value in column A of Sheet2 from row 4 to the last used row. If a match is found, it will apply a Yellow Background color to cell A2 in Sheet1.

Excel VBA Code:

Sub AlternativeWayOfConditionalFormatting()
    
    Dim ws1, ws2 As Worksheet
    Dim i, lastRow As Long
    
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    lastRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 4 To lastRow
        
        If ws1.Range("A2").Value = ws2.Range("A" & i).Value Then
            
            ws1.Range("A2").Interior.Color = RGB(255, 255, 0)
            
        End If
        
    Next i
    
End Sub

I am also giving you the Solution Workbook to help you understand better. Don't hesitate to contact us again with any other inquiries or concerns.

Regards
Lutfor Rahman Shimanto