Select all colored cells

[Guest]

I am working on a code in visual basic for a macro in Excel. In the worksheet, I have an area that is green. What I want the program/macro to do is copy a formula in an existing cell, select all the colored cells (all cells are the same color) and paste the formula in the colored cells. Is this possible? If so, what does the code look like?

Thank you!

 

[Dave Peterson]

This might get you started:

Option Explicit
Sub testme03()

Dim subRng As Range
Dim cellWithFormula As Range
Dim SuperRng As Range
Dim myCell As Range

With ActiveSheet
Set SuperRng = Selection
For Each myCell In SuperRng.Cells
If myCell.Interior.ColorIndex = 4 Then 'lots of greens--be careful
If subRng Is Nothing Then
Set subRng = myCell
Else
Set subRng = Union(myCell, subRng)
End If
End If
Next myCell

If subRng Is Nothing Then
'do nothing
Else
Set cellWithFormula = .Range("b9") 'chosen at random
subRng.FormulaR1C1 = cellWithFormula.FormulaR1C1
End If
End With

End Sub

 

[Guest]

Dave-

I tried using the macro that you sent. It didn't work. Nothing happened when I ran it. Thank you for the suggestion though. I appreciate it. If you have any more suggestions, please let me know.

Thanks.

----- Dave Peterson wrote: -----

This might get you started:

Option Explicit
Sub testme03()

Dim subRng As Range
Dim cellWithFormula As Range
Dim SuperRng As Range
Dim myCell As Range

With ActiveSheet
Set SuperRng = Selection
For Each myCell In SuperRng.Cells
If myCell.Interior.ColorIndex = 4 Then 'lots of greens--be careful
If subRng Is Nothing Then
Set subRng = myCell
Else
Set subRng = Union(myCell, subRng)
End If
End If
Next myCell

If subRng Is Nothing Then
'do nothing
Else
Set cellWithFormula = .Range("b9") 'chosen at random
subRng.FormulaR1C1 = cellWithFormula.FormulaR1C1
End If
End With

End Sub

 

[Dave Peterson]

If you really wanted to select the cells, change these lines:

If subRng Is Nothing Then
'do nothing
Else
Set cellWithFormula = .Range("b9") 'chosen at random
subRng.FormulaR1C1 = cellWithFormula.FormulaR1C1
End If

to

If subRng Is Nothing Then
'do nothing
Else
subRng.Select
End If

And make sure you changed the .colorindex to what you really wanted.

One way to make sure you have that correct is to select the cell with the color
you want.
Then alt-F11 (to get to the VBE)
then ctrl-G (to see the immediate window)
then type this (and hit enter)
?activecell.interior.colorindex

Then use that number in the code.