Help needed! to check for presence of cell color

[fountainwood]

I need help here!
I want to create some code that checks all the cells in a row to find if any cell has a background color and if it finds any colored cell, to then set the first column of that row to that color.

desperate!

 

[Scott Huish]

This is for row 1, change as necessary:

Sub test()
Dim c As Range
For Each c In Range("A1", Cells(1, Columns.Count))
    If c.Interior.ColorIndex <> xlNone Then
        Range("A1").Interior.ColorIndex = c.Interior.ColorIndex
        Exit For
    End If
Next
End Sub

 

[Oaktree]

If the version of Excel you're using has it, the Application.FindFormat approach would likely be noticably faster than looping through each cell in a large range.

See http://www.ozgrid.com/forum/showthread.php?t=39823 for one example

 

[Scott Huish]

I had thought of that, but how you would look for any color, rather than a specific one? That's why I looped.

 

[fountainwood]

HotPepper,
'How do I get that to work for all rows in the sheet?

 

[Scott Huish]

If you are on XL2003 or prior, there are 65536 rows, do you really need to check that many, or is there a smaller range you need to check?

 

[fountainwood]

Much smaller - but variable week on week.

 

[fountainwood]

I guess UsedRange would be of use but don't know enough to use it.

 

[Oaktree]

Are there specific colors which would be used? If there are say 5 color possibilities, it would save you a lot of time...

What happens if there is more than one colored cell in a row?

 

[lenze]

Sub Test()
Dim c As Range
For Each c In Sheet1.UsedRange
If c.Interior.ColorIndex <> xlNone Then Cells(c.Row, 1).Interior.ColorIndex = c.Interior.ColorIndex
Next c
End Sub

lenze