Help with replacing formula

[wonderd]

Hello everyone. I am trying to create formula and need a little help.

I am working with Columns K and R. I need a formula that if any cell in column K has the word Blue. The formula clears any text in the corresponding R cell and puts the word Black

So when the formula runs. If K24 has the word Blue, then any text in R24 is deleted and replaced with the word Black.

[AlKey]

Unfortunately, formulas in excel cannot remove/delete or insert anything in any cell other than the cell where formula is written• For what you asking you need VBA•

[wonderd]

How would this be done via VBA ?

[morevijay1982]

Sub Replacecell()
Dim lRow As Long


lRow = 1

Do While (Cells(lRow, "K") <> "")
Range(Cells(lRow, "k"), Cells(lRow, "k")).Select



If ActiveCell.Value = "blue" Then
Range(Cells(lRow, "r"), Cells(lRow, "r")).Select
ActiveCell.Value = "Black"
Range(Cells(lRow, "k"), Cells(lRow, "k")).Select
'Selection.Insert Shift:=xlDown

End If
lRow = lRow + 1
Loop


End Sub

[FDibbins]

morevijay1982 please use code tags when posting code (see my signature for how to)