have VBA select cells based on Fill color

[andrewb90]

Hello all,

I have a vba code that is used to fill in various cells at the users request. However, I need to have vba search a cell range (E89:R207) and any cells that have this fill color:

    With .Interior        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Would need to be selected so that I can call another macro, but I can't figure out how to make it work. Any help would be greatly appreciated.
I'm not sure if they could all be selected, or if each individual cell needs to be selected then have a my called macro run.

 

[dmt32]

Hi,
you should be able to use Union to do what you want without need to select any cells.

See if following helps:

Sub GetInteriorColor()
    Dim c As Range, InteriorColor As Range, DataRange As Range
    Dim ColorValue As Long
    Dim ws As Worksheet


    'change sheet name as required
    Set ws = ThisWorkbook.Worksheets("Sheet1")


    'range you are searching
    Set DataRange = ws.Range("E89:R207")
    
    ColorValue = 49407


    DataRange.EntireRow.Hidden = False


    For Each c In DataRange.Cells
        If c.Interior.Color = ColorValue Then
            If InteriorColor Is Nothing Then
                Set InteriorColor = c
            Else
                Set InteriorColor = Union(InteriorColor, c)
            End If
        End If
    Next c
    'pass range to another macro
    If Not InteriorColor Is Nothing Then AnotherMacro Target:=InteriorColor
End Sub




Sub AnotherMacro(ByVal Target As Range)
    MsgBox Target.Address
End Sub

Change the sheet name (shown in RED) where your data resides as required and the name of the macro you are calling also shown in RED which will need to include an argument to pass the range to.

Dave

 

[andrewb90]

So this code does call my other code, however I am still required to highlight cells which doesn't get me what I need.

To elaborate a little more on my project:
This sheet will have different cells ranges highlighted to indicate an employees unavailability. When it is highlighted on this sheet, it shows up on another sheet (the scheduling page)as unavailable. Now, every week I run a macro to clear all of the entered shifts and and request time off. This also clears the permanent availability that is set on the current page. Once that clears I need the code to look at this other sheet ("Master Availability") and select any cells that have that unique fill color and run the macro that makes those cells appear on my scheduling sheet.

I hope I am making sense