In Excel, how do I know if a certain value exist in a certain row and then highlight it?

Question

I have 2 sheets named SheetA and SheetB.

In SheetA, A2 is blank and will be filled with value like "Customer 1" or "Customer 2". B1 to F1 contains items like "Car", "Contract", "Pet", "Home", etc.

In SheetB, A1 to A80 contains "Customer 1" to "Customer 80". And then I have columb B to F contains items "Car", "Contract" or "Pet" randomly. This means that "Customer 1" will have "Car", "Contract" but "Customer 2" can only have "Pet".

What I want to do is to hightlight in Sheet 1, cell B2 to F2 whether if a customer has a car or a pet based on the info in SheetB and the name entered into Sheet 1 cell A2.

I think this can be done by using conditional formatting but I couldn't get the formula right.

Answer 1

I would suggest you use "=" in sheet1 across the whole matrix (B1:F27) to replicate values in sheet2, then do a conditional formating "equal to" --> not equal to value 0, hope it answers your question.

Answer 2

If I understand correctly you want to highlight the cell of the item that is owned by the customer named in SheetA!A2, and the column headings for SheetA match the column in which the item will be found in SheetB. So for example if SheetA!A2 contains Customer 2, then cell SheetA!D2 should be highlighted. If that is a correct understanding then the conditional formatting formula could be: =INDIRECT("SheetB!D"&MATCH($A2,SheetB!$A$1:$A$10,0))>0

Here is how I am understanding SheetA: and here is how I am understanding SheetB:

Answer 3

Maybe you can work with this code. This will go through all of your names on SheetB and compare them to cell A1 in SheetA. All you have to do is the highlighting stuff.

Sub Example()
    Dim counter As Integer
        Set counter = 0
    Dim cell As Range
        Set cell = "A1"
    Dim active As Range
        Set active = cell

    Do While counter <= 80
        active = Worksheets("SheetB").Range(cell)
        If active = Workseets("SheetA").Range("A2") Then
            'start doing your drawing'
        End
        counter = counter + 1
        cell = cell.Offset(1, 0)
    Loop       
End Sub