macro to insert formula if cell format criteria satisafied

[Guest]

I am trying to write an Excel macro that searches a column of cells to find a
specific cell format (fill color) and enter a formula in that same row, but 2
columns over everywhere that the fill color is found.

Does any have any suggestions on how to do this?

Thanks

 

[Leith Ross]

Hello Los,

Here is an example. This runs through A1:A100 on Worksheet1 looking for
Red cells. When found a formula is inserted 2 columns to the right. You
can change the worksheet name, range and formula to match your needs.


Code:
--------------------

Public Sub AddFormula()

Dim Rng As Range
Dim Formula As String

Set Rng = Worksheets("Sheet1").Range("A1:A100")
Formula = "= 2+2"

For Each Cell In Rng
If Cell.Interior.ColorIndex = vbRed Then
Cell.Offset(0, 2).Formula = Formula
End If
Next Cell

End Sub

 

[Guest]

Leith,
Thank you!
It worked with one small change. It did not recognize the color "vbRed". I
used the color index instead and it worked great.

Thanks for your help.

 

[Guest]

Is there a way to recognize the colour of the cell without using macro. For
eg, if the colour of a particluar cell (which is getting colored using
conditional formatting) is RED. Can I write (in an adjacent cell) - IF(cell
colour = RED, X,Y)