Write a formula depending on cell color

[Todd Bardoni]

Is there a way to write a macro that will find the yellow cells in my worksheet and insert the SUM function?

 

[Dave Patton]

On 2002-09-02 09:28, tbardoni wrote:
Is there a way to write a macro that will find the yellow cells in my worksheet and insert the SUM function?

How and what criteria did you use to make the cells color Yellow?

Do you mean the background color?

You could use the same criteria in your macro.

You can search for a User-Defined-Function UDF that can identify the cell's color.

 

[Todd Bardoni]

I formatted the cells yellow to highlight the totals, but the list of data is always different and I want a script that will find the yellow formatted cells and insert the SUM function. I'm sure its simple but I've never written any script that dealt with cell color so I'm pretty ignorant.

 

[Derek]

Hi Tibardoni

You might like to try this macro code. It covers the range A1:G50 , (change as required).
When it finds a yellow cell it sums all cells above it up to the first empty cell

Dim x As Range
For Each cell In Range("A1:G50")
If cell.Interior.ColorIndex = 6 Then
cell.Value = "=sum(" & cell.Offset(-1, 0).Address & ":" & cell.Offset(-1, 0).End(xlUp).Address & ")"
End If
Next
End Sub

Hope this gives you some ideas
regards
Derek