CODEWITHSUNDEEP
excelvba
ebgoodman
ebgoodman

Reputation: 1

Find cell value in another sheet and change to that cell interior color

I'm trying to have the selected cell on sheet1 (or every 8+11nth cell in column F to end) turn into the same interior color as the same value on sheet2 row2.

This is what I have so far. It's able to go to sheet2, but have trouble finding where the same value is at and selecting it.

Sub FindAndColor()

OA=ActiveCell.Address
rngY=ActiveCell.Value

    Sheets("Sheet2").Select
    Rows(2).Select
    Selection.find(What:=rngY, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
      
ActiveCell.Select
CC=ActiveCell.DisplayFormat.Interior.Color

Sheets("Sheet1").Select
OA.Select
ActiveCell.DisplayFormat.Interior.Color=CC

End Sub

Upvotes: 0

Views: 55

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

When using Find() you should test whether a match was made before proceeding.

So something like this should work:

Option Explicit

Sub FindAndColor()

    Dim f As Range, c As Range
    
    Set c = ActiveCell
    Set f = ThisWorkbook.Sheets("Sheet2").Rows(2).Find( _
                What:=c.Value, LookIn:=xlFormulas, _
                LookAt:=xlWhole, MatchCase:=False)
                
    If Not f Is Nothing Then 'found a match ?
        c.Interior.Color = f.DisplayFormat.Interior.Color 'DisplayFormat is read-only...
    Else
        'do something if no match?
    End If

End Sub

Upvotes: 1

Related Questions