Populating cell values based on cell color

[ldwilson]

I am hoping someone can help out here....

This may sem like an odd request

I am trying to populate a range of cells with a specific value based on the color of the cell. For example I have a data from cell A1:Z85. If any of those cells are green I want to replace the value with a value that is in cell A105.

Reason for the ask...I import weekly data and the data that is incomplete comes back in a green cell and i need to be able to change that

thanks in advance

[Mumps1]

There are different shades of green that may have been used so the color index value in this code may not be correct. You can use a simple macro to find the actual color index code used in your sheet and then replace the "43" with the value you get after running the macro. Run the first macro to get the correct color code. Replace the "B4" with any cell that is colored green. Then try the second macro to do what you were asking replacing the "43" with the correct code.

Sub FindColorCode()
    MsgBox Range("B4").Interior.ColorIndex
End Sub

Sub Test()
    Application.ScreenUpdating = False
    Dim c As Range
    For Each c In Range("A1:Z85")
        If c.Interior.ColorIndex = 43 Then
            c = Range("A105")
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

[ldwilson]

PERFECT...many thanks for the fast reply

[Mumps1]

My pleasure.