Using EXCEL to find shaded cells, and shading other cells if found

Question

Using EXCEL to reference the color of a cell and make changes to a second sheet dependent on that.

The general idea is to find whether or not a cell is shaded within a column(B:B) in one sheet(sheet1). If found shaded, find value of column f from the row of the shaded cell in column B of sheet 2. Highlight that row.

I haven’t the slightest idea how to start this.

Answer 1

Microsoft documentation:

Interior.Color property (Excel)

Range.Find method (Excel)

Application.Intersect method (Excel)

Option Explicit
Sub Demo()
    Dim oSht1 As Worksheet, oSht2 As Worksheet
    Dim ColB1 As Range, ColB2 As Range, c As Range, ce As Range
    Dim vValue
    Const HL_COLOR = vbYellow ' highlight color
    Set oSht1 = Sheets("Sheet1") ' update sheet name as needed
    Set oSht2 = Sheets("Sheet2")
    Set ColB1 = Application.Intersect(oSht1.UsedRange, oSht1.Columns(2))
    Set ColB2 = Application.Intersect(oSht2.UsedRange, oSht2.Columns(2))
    ' remove highlight on Sheet2
    oSht2.Cells.Interior.Color = xlNone
    For Each c In ColB1.Cells
        If c.Interior.Color <> vbWhite Then
            vValue = oSht1.Cells(c.Row, "F").Value
            Set ce = ColB2.Find(vValue, LookIn:=xlValues, lookat:=xlWhole)
            If Not ce Is Nothing Then
                ce.EntireRow.Interior.Color = HL_COLOR
            End If
            Set ce = Nothing
        End If
    Next
End Sub