macro to insert formula if cell format criteria satisafied
 

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
--
Los

macro to insert formula if cell format criteria satisafied
 

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

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=495687

macro to insert formula if cell format criteria satisafied
 

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.
--
Los


"Leith Ross" wrote:


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

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=495687

macro to insert formula if cell format criteria satisafied
 

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)

"Los in RI" wrote:

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.
--
Los


"Leith Ross" wrote:


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

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=495687